Ignore AM and PM - 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 2005 Forums

Transact-SQL (2005)

Ignore AM and PM

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-03-13 : 13:21:30

Hello,

I have a column in the table which contains date and time information.

It always has the format "mm/dd/yyyy 12:00:00 AM"
or "mm/dd/yyyy 12:00:00 PM".

Now the query is

select * from table1
where dt = @date + ' 12:00:00 PM'

How can I refelect both AM and PM cases?
Or can we use substring?

Thanks

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-03-13 : 15:08:42

May I use

select * from table1
where dt like @date + '%'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-13 : 15:15:28

Not really sure what you're after but
select * from table1
where dt >= @date and dt < dateadd(day,1,@date) will work and allow you to use any indexes on dt, assuming @date = "mm/dd/yyyy"

Jim

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-03-13 : 15:25:33

@date is a string type but dateadd() is a datetime type.
Not sure right or not?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-14 : 03:44:12

where dateadd(d,datediff(d,0,dt),0) = '2008-09-08'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-14 : 03:47:27

And again bklr, please stop advicing this because of bad practive and it will also prevent usage of an existing index over dt column!

Try this instead

select * from table1
where dt >= @date
and dt < DATEADD(DAY, 1, @date)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources