tsql date - SQL Server Forums

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

tsql date

Author

Topic

zhtway1
Starting Member

10 Posts

Posted - 2010-08-21 : 05:04:16

Hi,

I wanna retrieve records for today. But not working?
Any idea?

select freedate from holidays where freedate = (getdate())

how can I retrieve for specific date?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-21 : 05:50:06

depending on your freedate datatype you should consider several options.
you don't get results because you most probably don't have any records inserted at the very moment when you run select.
try this:


select freedate from holidays where convert(date, freedate,120) = convert(date, getdate(), 120))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-21 : 08:54:08

is the freedate column contain time also ?

if it does not . .


select freedate
from   holidays
where  freedate = dateadd(day, datediff(day, 0, getdate()), 0)

if it contain time
select freedate
from   holidays
where  freedate >= dateadd(day, datediff(day, 0, getdate()), 0)
and    freedate <  dateadd(day, datediff(day, 0, getdate()), 1)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 13:11:01

quote:
Originally posted by slimt_slimt

depending on your freedate datatype you should consider several options.
you don't get results because you most probably don't have any records inserted at the very moment when you run select.
try this:

select freedate from holidays where convert(date, freedate,120) = convert(date, getdate(), 120))

this method wont use an index if present in freedate column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-22 : 07:30:07

visakh: what do you mean that i won't use index?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 12:20:51

quote:
Originally posted by slimt_slimt

visakh: what do you mean that i won't use index?

because you're using function over freedate column any index if present on it wont be used. so if you want to take advantage of an index created on freedate column you need to use khtans method.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-22 : 13:01:28

thank you. didn't know that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-22 : 13:07:04

ok no probs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources