speedyraz speedyraz - 1 year ago 91
SQL Question

VBA queries return data beyond end date

I am running a VBA query which returns data beyond end date .

The code is as follows:

startdate = Format(Sheets("sheet1").Range("B2").Value, "####")
enddate = Format(Sheets("sheet1").Range("B3").Value, "####")

Set conn = New ADODB.connection
Set rs = New ADODB.Recordset
connection = "provider = sqloledb ; data source = &user; initial catalog = &data ; integrated security = sspi ; "
conn.Open connection
Set rs = conn.Execute("select * from sale_table where datetime >= " & startdate & " and datetime <= " & enddate & "")

If Not rs.EOF Then
Sheets(1).Range("I12").CopyFromRecordset rs
MsgBox ("no record")
End If

if enddate = 31/08/2016
result shown upto 02/09/2016 ( two days beyond end date)

Answer Source

Your date formatting is the problem:

startdate = Format(Sheets("sheet1").Range("B2").Value, "####")
enddate = Format(Sheets("sheet1").Range("B3").Value, "####")

These will produce date serials, or the number of days elapsed since January 1st, 1900. For 31/08/2016 (08/31/2016 for those in the US), your enddate will be 42613 when using Excel.

In SQL Server, you can either use DATEADD(DAY, ..., ...) or CAST( ... AS DATETIME) to convert this to a date. Implicitly, when you perform a logical operation against a date serial i.e. <= enddate or >= startdate it'll convert that serial into a datetime.

However, if you have tried DATEADD or CAST on 42613 in SQL Server you'll notice it returns 02/09/2016 (US: 09/02/2016) which is why your returned data is beyond the end date. Also, if you tried DATEDIFF(DAY, '2016-08-31', '1900-01-01') it returns 42611 on SQL Server; 2 days difference to Excel's interpretation of 31/08/2016 date serial.

Why is this?

Mr Chip Pearson explains it in Dates and Times in Excel, but I'll summarize it. Keep in mind that when I say date serial I mean days elapsed since 01/01/1900. Both in Excel and SQL Server, default date value is 01/01/1900. However:

Excel stores dates and times as a number representing the number of days since 1900-Jan-0

00/01/1900 (US: 01/00/1900) is not a valid date. So already we have to offset any Excel date serial by -1. Why so?

The number 1 represents 1900-Jan-1. It should be noted that the number 0 does not represent 1899-Dec-31. Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design.

So, in your particular case, since date serial 1 represents 01/01/1900 in Excel (because date serial 0 is 00/01/1900) which is first of your extra 2 days and the leap year 2000 has 366 days rather than 365 which is your second extra day, that's why it's returning data up to and including 02/09/2016.

In short, you need to account for Excel's date representation as being days from 00/01/1900 rather than from 01/01/1900 and also any leap years when using an Excel date serial in a SQL Server statement.