Welcome to WebmasterWorld Guest from 35.171.45.91

Forum Moderators: ocean10000

Message Too Old, No Replies

SQL Server 2000 between 2 dates

     
5:30 pm on Feb 12, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 9, 2005
posts:240
votes: 0


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?

5:49 pm on Feb 12, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Dec 15, 2003
posts:2645
votes: 7


What is the data type of Tasks.DateCreated?
11:14 pm on Feb 12, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Dec 19, 2006
posts:92
votes: 0


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]

6:16 am on Feb 13, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 13, 2005
posts:1078
votes: 0


You need to use the DateDiff function in SQL:

DATEDIFF ( datepart , startdate , enddate )

datepart in you query would be dd for day.

Chip-

9:57 am on Feb 13, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 9, 2005
posts:240
votes: 0


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]

10:25 am on Feb 13, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Dec 19, 2006
posts:92
votes: 0


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]

10:32 am on Feb 13, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 9, 2005
posts:240
votes: 0


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.

10:40 am on Feb 13, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Aug 9, 2005
posts:240
votes: 0


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]

10:42 am on Feb 13, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Dec 19, 2006
posts:92
votes: 0


[msdn.microsoft.com...]

Not sure if that link will be allowed..

5:12 pm on Feb 13, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 13, 2005
posts:1078
votes: 0


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-