how to query the datetime field in sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to query the datetime field in sql?

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2009-07-14 : 23:57:37

i have a datetime field in table tl, how to list out the specific date records?
the records are

2009-07-08 17:41:35.810
2009-07-10 17:41:35.810
2009-07-14 17:41:35.810

i want to list out the 14th date records
query wont accept the hyphens,

select * from t1 where myfield like '%14%' will display both 14th and 10th records, and also

select * from t1 where myfield like '2009_07_14%' has no effect.

thanks
subha

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-14 : 23:59:52

hi, you may use this
SELECT *
FROM t1
WHERE DATEPART(DAY,myfield) = 14

Hope can help...but advise to wait pros with confirmation...

subhaoviya
Posting Yak Master

135 Posts

Posted - 2009-07-15 : 00:20:28

thanks waterduck
its working fine

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 00:21:15


declare @date datetime

select @date = '20090714'

select *
from   t1
where  myfield >= @date
and    myfield <  dateadd(day, 1, @date)

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-15 : 00:21:33

welcome ^^, lucky nothing mistake

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-15 : 00:22:50

erm....sifu...urs query is to catch 12am?

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-15 : 00:24:24

quote:
Originally posted by waterduck

welcome ^^, lucky nothing mistake

Hope can help...but advise to wait pros with confirmation...

Nope. But if possible, you want to avoid using a function on a column. It is bad for performance. If there is an index on the column, SQL Server will not be able to use it.

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-15 : 00:34:57

subhaoviya!!! PLEASE FOLLOW KHTAN QUERY!!! MY QUERY DOESN'T USE INDEX WHICH EVENTUALLY SLOW DOWN PERFORMANCE!!!

Hope can help...but advise to wait pros with confirmation...

subhaoviya
Posting Yak Master

135 Posts

Posted - 2009-07-15 : 00:59:36

ok waterduck!!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-15 : 01:26:00

so sorry...im still beginner x.X

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources