How do I query a column in which it's datetime format is yyyy-mm-dd hh:mm:ss:mmm e.g. '2010-12-11 12:00:00.000' using Where = '12/11/2010 12:00:00 AM'

Question

Hi Guys,

The Code is Shown below.

 ----------------------------------------------------------------------------------------------------------------------------------------------

Select * from WSCAN2010 WHERE matnum ='C4918' AND ordRefno = 'CSO-10000002' AND delDate = CONVERT(varchar(30), '12/11/2010 12:00:00 AM', 111) <----

 ----------------------------------------------------------------------------------------------------------------------------------------------

I cant get any result using this 'CONVERT(varchar(30), '12/11/2010 12:00:00 AM', 111)' as the format of the date in delDate Column in the WSCAN2010 is: 2010-12-11 12:00:00.000

but if use '12/11/2010 12:00:00.000' it works

Answer 1

you want to exactly match the time also or only the day??? If you wants to match only the day u can write like

 

declare @date datetime = '12/11/2010'
Select * from WSCAN2010 WHERE matnum ='C4918' AND ordRefno = 'CSO-10000002' AND delDate >= @date and deldate < @date + 1

Answer 2

Can you try the following? Let us know if works.

SELECT CONVERT(varchar(30), convert(datetime,'12/11/2010 12:00:00 AM'), 121)
-- 2010-12-11 00:00:00.000

Is the datetime a string in the table? It is better to store it datetime/date format for data integrity and performance reasons.

More on datetime conversion: http://www.sqlusa.com/bestpractices/datetimeconversion/

---

Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM

Answer 3

Usually ISO format works:

select * from table where datevalue = '20101112 00:00:00' -- assuming 12am means midnight

or

select * from table where datevalue = '20101112 12:45:00'

If you want to query everything that happend during a date you have to query a date range:

select * from table where datevalue >= '20101112' and datevalue < '20101113'

Answer 4

try this one:

Select * from WSCAN2010
WHERE matnum ='C4918' AND ordRefno = 'CSO-10000002' AND
convert(varchar(20),delDate ,101)= CONVERT(varchar(30), convert(datetime,'12/11/2010 12:00:00 AM'),101)

 

---

--------------------------------- Devender Bijania

Answer 5

Hi Ramir,

Thanks for the help :)

Answer 6

Hi,

Thank you soo much :)

Answer 7

Hi Manfred,

Thank you for your answer... :)

Answer 8

Hi Davender,

Thank you :)

Answer 9

Hi Peso,

Thank you sooo much again :)