Tim Tim - 3 months ago 12
SQL Question

SQL Selecting all data between date ranges when date is stored as nvarchar

I inherited a T-SQL table with

Start_Date
and
End_Date
columns that are
nvarchar
data types. I need to select all the records for a particular day who's start date is before that date but have an end date after that date.

Another wrinkle is that some people are still current and do not have an
end_date
. Sadly, I cannot just convert the data type of the column to a date. I am trying to write this in VBA because


  1. my boss understands Excel, and

  2. the reports he is doing must be sent as Excel



For example:

Name Start_Date End_Date
Joe 12/1/2014 6/15/2016
Bob 8/28/2013 6/5/2014
Jon 1/27/2015 12/18/2015
Tim 8/28/2013


If my selected date were 8/15/2015, my returned data should be

Name Start_Date End_Date
Joe 12/1/2014 6/15/2016
Jon 1/27/2015 12/18/2015
Tim 8/28/2013


I've tried the following queries, but they return
Run-time error -2147217900 (80040e14) Automation error


SELECT NAME
FROM INFORMATION
WHERE CONVERT(datetime, START_DATE, 101) >= '12/1/2015'
AND CONVERT(datetime, END_DATE, 101) <= '12/31/2015'

SELECT NAME
FROM INFORMATION
WHERE START_DATE >= '12/1/2015'





Edit:
I am using the following code to construct my query:

For intYear = Year(Now()) To 2012 Step -1
For intMonth = 12 To 1 Step -1
FirstDayOfMonth = DateSerial(intYear, intMonth, 1)
LastDayOfMonth = DateSerial(intYear, intMonth + 1, 0)
SQL = "SELECT NAME FROM dbo.INFORMATION " & _
"WHERE CONVERT(DATETIME, START_DATE, 101) <= " & FirstDayOfMonth & " AND " & _
"CASE END_DATE WHEN '' THEN GETDATE() " & _
"ELSE CONVERT(DATETIME, END_DATE, 101) END >= " & LastDayOfMonth
Debug.Print SQL
'Output:
'SELECT NAME FROM dbo.INFORMATION WHERE CONVERT(DATETIME, START_DATE, 101)
'<= 12/1/2016 AND CASE END_DATE WHEN '' THEN GETDATE() ELSE
'CONVERT(DATETIME, END_DATE, 101) END >= 12/31/2016
Set rs = cn.Execute(SQL)
Next intMonth
Next intYear





Update 2:

I set
SQL
to the literal query rather than dynamically generating it and put in error catching (which I really should have done long ago). I think putting single quotes around the dates will cause it to be a string rather than a date, but I tried it both ways.

SQL = "SELECT EMPLOYEE_NAME FROM dbo.INFORMATION " & _
"WHERE CONVERT(DATETIME, START_DATE, 101) <= 12/01/2016 " & _
"AND CASE END_DATE " & _
"WHEN '' THEN GETDATE() " & _
"ELSE CONVERT(DATETIME, END_DATE, 101) " & _
"END >= 12/31/2016"


Both with and without single quotes the actual SQL error from SSMS is:

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.


Looking at the data in the table, I can see that some dates are NULL, some are blank, and some are mm/dd/yy (or even m/d/yy). I wanted SQL to do all the heavy lifting, but now I'm trying to determine if it will be tougher to
SELECT *
and deal with it in Excel or engage my boss, his peers, and my grandboss to 'fix' the table.

Answer

You need to switch your >= and <=. Right now you're looking for people with start dates AFTER your target date and end dates BEFORE your target date. But I assume that's just a typo in your example.

You're on the right track though. When you say you want to do this in VBA, I'd caution against doing any logic in VBA. Do all of your filtering in SQL, and just use VBA to call the SQL. Also, don't forget that you can connect Excel directly to SQL as a data source and skip VBA.

Anyway, your SQL query should look something like this:

SELECT  Name
FROM    dbo.INFORMATION
WHERE   CONVERT(DATETIME, Start_Date, 101) <= @targetdate
        AND CONVERT(DATETIME, End_Date, 101) >= @targetdate

But then you have the case of someone with no end date. Let's fix it so it all fits in one query.

SELECT  Name
FROM    dbo.INFORMATION
WHERE   CONVERT(DATETIME, Start_Date, 101) <= @targetdate
                    AND     CASE End_Date
                              WHEN '' THEN GETDATE()
                              ELSE CONVERT(DATETIME, End_Date, 101)
                            END >= @targetdate

Here we're using their end date if they have one, and if they don't we're using now. If your target date could ever be in the future, then you could use an arbitrarily large date instead.

One last item is to make it more readable, using BETWEEN:

SELECT  Name
FROM    dbo.INFORMATION
WHERE   @targetdate BETWEEN CONVERT(DATETIME, Start_Date, 101)
                    AND     CASE End_Date
                              WHEN '' THEN GETDATE()
                              ELSE CONVERT(DATETIME, End_Date, 101)
                            END
Comments