How to get date from datetime in sql

Question

I have a field named 'CreatedDate' in table.It contain value as 2009-12-07 11:02:20.890.My search parameter is createddate.Parameter contains value as 2009-12-07.My parameter contains only datepart.If my parameter contains only 2009-12-07 its not giving result.Only when time is included its giving result. My query is Select * from STD_Enquiry where CreatedDate='2009-12-07 Can anybody give approppriate query to get the result?

score 5 · Answer 1 · answered Dec 08 '09 at 04:21

5

Compare only on the date portion (without time):

WHERE DateColumn >= DATEADD(day, DATEDIFF(day, 0, @SomeDateParam), 0)

answered Dec 08 '09 at 04:21

Mitch Wheat

295,962
43
465
541

1

Yes. But if you include an explanation (or a link to one) on *why* this works, it'd help him more. – Philip Kelley Dec 08 '09 at 04:26
@Phillip, ok thanks: http://mitch-wheat.blogspot.com/2007/05/sql-server-compare-date-part-of.html – Mitch Wheat Dec 08 '09 at 05:07
And this too: http://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime – gbn Dec 08 '09 at 06:03
@gn: nice ref. to timings of the techniques. – Mitch Wheat Dec 08 '09 at 06:17

score 0 · Answer 2 · answered Dec 08 '09 at 04:22

0

Try this:

Select * from STD_Enquiry where CreatedDate like '2009-12-07%'

answered Dec 08 '09 at 04:22

Dumb Guy

3,336
21
23

2

that is not the way I'd recommend! – Mitch Wheat Dec 08 '09 at 04:23

score 0 · Answer 3 · answered Dec 08 '09 at 05:36

0

Use this query to comapare only date

Select * from STD_Enquiry where to_char(CreatedDate,'mmddyyyy') = '09102007'

answered Dec 08 '09 at 05:36

Vijay

65,327
90
227
319

score 0 · Answer 4 · answered Dec 08 '09 at 06:09

0

Select * from Table where CreatedDate= ''Datepart(yyyy, CreatedDate) + '-' + Datepart(mm,CreatedDate) + '-' + Datepart(DD,CreatedDate)''

answered Dec 08 '09 at 06:09

MSH

11
1

How to get date from datetime in sql

4 Answers4