Comparing datetime field with "like"

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

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing datetime field with "like"

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-15 : 10:24:14

Keith writes "I have a column that is a datetime type that contains records such as:
6/29/2004
6/29/2004 7:34:26 AM
6/30/2004
6/30/2004 7:29:12 AM
6/30/2004 7:56:13 AM
6/30/2004 7:44:02 AM

I am trying to get all records that are on 6/30/2004 regardless of the time. I have tried:
"select datecolumnname from tablename where cast(datecolumnname as varchar(50)) like '6/30/2004%'"
and other close variations but these will only return the '6/30/2004' record, and not the ones with the time.

I know I can get the records if the datecolumnname is greater than '6/29/2004 23:59:59' and datecolumnname is less than '7/1/2004 00:00:01' but I was looking for something a little bit cleaner and less effort.

I am using Windows 2000 and SQL Server 2000 with the latest service packs.

Thanks in advance,
Keith"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-15 : 10:34:15

WHERE DATEDIFF(dd, <your date field>, <requested date>) = 0

select datediff(dd, '6/30/2004 7:44:02 AM', '6/30/2004')
select datediff(dd, '6/30/2004 00:00:00 AM', '6/30/2004')
select datediff(dd, '6/30/2004 11:59:59 PM', '6/30/2004')
select datediff(dd, '7/1/2004 00:00:00 AM', '6/30/2004')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources