mm/dd/yyyy hell - 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

SQL Server 2000 Forums

SQL Server Development (2000)

mm/dd/yyyy hell

Author

Topic

nilaavu
Starting Member

18 Posts

Posted - 2006-03-24 : 15:02:31

In my query
select workingpunch_ts from tempP10 where
convert(VARCHAR , tempp10.workingpunch_ts,101) = '3/26/06'

this does not work

but if I pass '03/26/06' works.
convert(VARCHAR , '3/26/06',101)
convert(VARCHAR , '3/26/06',101) all giving me '3/26/06'
how can I do that?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-24 : 15:20:00

try
Select tempp10.workingpunch_ts from tempP10 where Datediff(day,tempp10.workingpunch_ts,'3/26/06')=0

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-24 : 15:37:26

This should make better use of indexes:

WHERE workingpunch_ts >= CONVERT(datetime, '03/26/2006')
AND workingpunch_ts < CONVERT(datetime, '03/27/2006')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-27 : 01:26:47

http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources