SQL Server 2000 between 2 dates

[FiRe]

I need to select records between 2 dates...

dMonday = CDate("2/9/2009 01:00:00")
dFriday = CDate("2/13/2009 11:59:59")

SQL = "SELECT Tasks.*, Users.Name FROM Tasks INNER JOIN Users ON Users.UserId = Tasks.UserId WHERE (Tasks.DateCreated >= '" & dMonday & "' AND Tasks.DateCreated <= '" & dFriday & "')"

I am getting:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
/fl1tasks/load.asp, line 35

Any ideas?

[Demaestro]

What is the data type of Tasks.DateCreated?

[darrenG]

try:

..." & dFriday.ToString("yyyyMMdd HH:mm:ss") & "...

On each date variable being placed into the string

[edited by: darrenG at 11:15 pm (utc) on Feb. 12, 2009]

[carguy84]

You need to use the DateDiff function in SQL:

DATEDIFF ( datepart , startdate , enddate )

datepart in you query would be dd for day.

Chip-

[FiRe]

Its a smalldatetime data type.

carguy84 can you give me an example I cant quite get it to work :(

*edit: I want to get all records between 2 dates, not the length of time between 2 dates

[edited by: FiRe at 9:58 am (utc) on Feb. 13, 2009]

[darrenG]

Fire, did you try formatting the dates as I suggested? IF so and no joy, try without the seconds:

.ToString("yyyyMMdd HH:mm")

If the seconds are critical to the comparison, you may have to move forward to:

CDate("3/13/2009 00:00")

for example

[edited by: darrenG at 10:26 am (utc) on Feb. 13, 2009]

[FiRe]

No I just get:

Object required: 'dFriday'

This seems to work:

SELECT Tasks.*, Users.Name FROM Tasks INNER JOIN Users ON Users.UserId = Tasks.UserId WHERE (Tasks.DateCreated >= '20090209') AND (Tasks.DateCreated <= '20090213')

But I need to compare the time as well.

[FiRe]

Further to this I now have:

SELECT Tasks.*, Users.Name FROM Tasks INNER JOIN Users ON Users.UserId = Tasks.UserId WHERE Tasks.DateCreated BETWEEN '20090209 00:00:00' AND '20090213 23:59:00'

Is this accurate?

[edited by: FiRe at 10:41 am (utc) on Feb. 13, 2009]

[darrenG]

[msdn.microsoft.com...]

Not sure if that link will be allowed..

[carguy84]

SELECT Tasks.*, Users.Name FROM Tasks INNER JOIN Users ON Users.UserId = Tasks.UserId WHERE Tasks.DateCreated BETWEEN '20090209 00:00:00' AND '20090213 23:59:00'

You can remove the spaces and the ':' and then you'd just be dealing with ints.

Chip-