Format Time? 2

[puregoldeneye]

Hi all,

I have a datetime field called date_hour. Currently the values that are being stored in this field are in this format: 2003-04-04 08:00:00.000. What should I write in my Select statement so that the query returns that value: 8 AM

Please Help!

Thanks in advance.

 

[puregoldeneye]

I got the answer, here it is:

SELECT cast(((DatePart(Hour,date_hour)-1) % 12)+1 as varchar(2)) + CASE when DatePart(Hour,date_hour)<12 then 'AM' else 'PM' end from sum where date_hour >='2003-04-04 08:00:00.000' and date_hour <= '2003-04-04 20:00:00.00' and sensorid= '5001' and direction = '1' order by date_hour asc

out put is: 8AM

Cheers!

 

[puregoldeneye]

I got the answer, here it is:

SELECT cast(((DatePart(Hour,date_hour)-1) % 12)+1 as varchar(2)) + CASE when DatePart(Hour,date_hour)<12 then 'AM' else 'PM' end

out put is: 8AM

Cheers!

 

[SQLBill]

First, I suggest you read this FAQ: FAQ183-3007. SQL Server does not store the date and time as you stated. The FAQ and the BOL explain it fully, so I won't.

Second, use the INDEX tab in the BOL and enter CONVERT. That will give you the format.

Third, datetime conversion is covered almost daily in this forum. Do a keyword search and you'll probably find your answer.

But, this is what you are looking for:

select convert(varchar(10), date_hour, 120) + ' ' + right(convert(varchar,date_hour,0),7)

-SQLBill

 

[puregoldeneye]

Hi SQLBill,

I agree with everything you have mentioned above.

Thanks!