Format of 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

SQL Server 2005 Forums

Transact-SQL (2005)

Format of date

Author

Topic

Guestuser18
Starting Member

34 Posts

Posted - 2009-10-28 : 07:04:20

Hi Guys

I am trying to run a query with criteria specifying a date.

exmaple: select * from table where actiondate like '%13/06/2008%'

The data in the table is displayed as 2008-06-13 10:33:42.943

But when I run the query nothing is returned. What format does the date in my query need to be in?
Thanks in advanace.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-28 : 07:07:13

Use this format for dates: YYYYMMDD

Guestuser18
Starting Member

34 Posts

Posted - 2009-10-28 : 07:12:13

Thanks for the swift reply.
However that didnt work what you suggested.
I tried - select * from table where actiondate like '%2008-06-13%'

Nothing was returned again.
ANy other suggestions?

thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-10-28 : 07:18:38

Hi try this once,

select * from table where actiondate = '6/13/2008'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-28 : 07:21:05

select * from table where actiondate >= '20080613' and actiondate < '20080614'

No, you're never too old to Yak'n'Roll if you're too young to die.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-28 : 07:22:31

Try:
select * from table where actiondate between '20080613 00:00' and '20080613 23:59'

Guestuser18
Starting Member

34 Posts

Posted - 2009-10-28 : 08:16:07

Thanks guys.

I tried Nageswar9 suggestion but that didnt work.
However webfred your suggestion did work.

Cheers!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-28 : 08:20:40

quote:
Originally posted by YellowBug

Try:
select * from table where actiondate between '20080613 00:00' and '20080613 23:59'

This is not reliable
See webfred's reply

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-10-30 : 02:29:17

quote:
Originally posted by Guestuser18

Thanks guys.

I tried Nageswar9 suggestion but that didnt work.
However webfred your suggestion did work.

Cheers!

tRY THIS
select * from table where CONVERT(VARCHAR(32),actiondate,101) = '6/13/2008'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 04:15:26

quote:
Originally posted by Nageswar9

quote:
Originally posted by Guestuser18

Thanks guys.

I tried Nageswar9 suggestion but that didnt work.
However webfred your suggestion did work.

Cheers!

tRY THIS
select * from table where CONVERT(VARCHAR(32),actiondate,101) = '6/13/2008'

You dont need to convert into varchar and compare. This will not work in all the case. See webfred's reply

Madhivanan

Failing to plan is Planning to fail

parody
Posting Yak Master

111 Posts

Posted - 2009-10-31 : 11:21:01

Also converting the date when it isnt necessary will prevent use of any indexes on the date column. Always best to write your logic on the "right" side of the predicate.

quote:
Originally posted by madhivanan

quote:
Originally posted by Nageswar9

quote:
Originally posted by Guestuser18

Thanks guys.

I tried Nageswar9 suggestion but that didnt work.
However webfred your suggestion did work.

Cheers!

tRY THIS
select * from table where CONVERT(VARCHAR(32),actiondate,101) = '6/13/2008'

You dont need to convert into varchar and compare. This will not work in all the case. See webfred's reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources