Query on date/time field for next day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query on date/time field for next day

Author

Topic

robert693
Starting Member

42 Posts

Posted - 2009-08-18 : 12:24:08

Hello,

I have a Date/Time field that I want to query for all records that fall on the day following the day the query is run. In other words,the field name: Appointment Date/Time, the query will sho all of the appointments for tomorrow. Do I use the GetDate() function for this?

Thank you for any help!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-18 : 12:41:45

[code]
WHERE AppointmentDateTime >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1)
AND AppointmentDateTime < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 2)
[/code]

robert693
Starting Member

42 Posts

Posted - 2009-08-18 : 13:29:39

Thank you!!

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-19 : 00:04:47

(Or) Use like this also

select * from urtable where Appointmentdatetime = DATEADD(D,DATEDIFF(D,0,GETDATE()+1),0)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 00:08:15

quote:
Originally posted by Nageswar9

(Or) Use like this also

select * from urtable where Appointmentdatetime = DATEADD(D,DATEDIFF(D,0,GETDATE()+1),0)

You will have problem if Appointmentdatetime contains the time. Which most likely does judging by the name of the column and the nature of the application

KH
[spoiler]Time is always against us[/spoiler]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-19 : 00:11:29

OK kHTAN,

select * from urtable
where Appointmentdatetime = CONVERT(VARCHAR(32),DATEADD(D,DATEDIFF(D,0,GETDATE()+1),0),101)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-19 : 00:17:34

how does CONVERT(VARCHAR(32),DATEADD(D,DATEDIFF(D,0,GETDATE()+1),0),101) diff from DATEADD(D,DATEDIFF(D,0,GETDATE()+1),0) ?

i mean the column Appointmentdatetime most likely will contain date and time, like '2009-08-20 08:15:00'

So you have to check for


WHERE Appointmentdatetime >= '2009-08-20'
AND   Appointmentdatetime <  '2009-08-21'

as what Ifor has posted.

KH
[spoiler]Time is always against us[/spoiler]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-19 : 01:49:32

ok,

where CONVERT(VARCHAR(32),Appointmentdatetime,101) = CONVERT(VARCHAR(32),DATEADD(D,DATEDIFF(D,0,GETDATE()+1),0),101)

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-08-19 : 02:47:58

Nageswar,
The solution provided by Ifor is the better one given that if there is an Index defined on the Appointmentdatetime column.
With CONVERT(VARCHAR(32),Appointmentdatetime,101), SQL Server won't be able to use Index defined on the Appointmentdatetime.
It is always a good practise to avoid the CONVERT function, or for that matter any function in WHERE clause on a column from table.
This one looks extra work but it will perform much better compare to CONVERT(VARCHAR(32),Appointmentdatetime,101)
WHERE AppointmentDateTime >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1)
AND AppointmentDateTime < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 2)

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources