My sample query is
SELECT D30.SPGD30_LAST_TOUCH_Y
from CSPGD30_TRACKING D30
My given date format is like "2013-01-01 00:00:00.000"
. I need to convert this date format to "mm/dd/yyyy hh:mm AM/PM"
. Do you have any idea about that?
Get time format of datetime in sql. ORDER BY expr1 ; SUBSTRING(CONVERT(varchar(20), yourdate, 22), 18, 3)-->it gives you a am/pm.
By using format code as 111 we can get datetime in “YYYY/MM/DD” format. By using format code as 112 we can get datetime in “YYYYMMDD” format. By using format code as 113 we can get datetime in “DD MMM YYYY HH:mm:ss: fff” format. By using format code as 114 we can get datetime in “HH:mm:ss: fff” format.
I think there is no single format to give them both. Try this using Convert
; Sql-Demo
declare @mydate datetime = getdate()
select convert(varchar(10),@mydate, 101) + right(convert(varchar(32),@mydate,100),8)
| COLUMN_0 |
----------------------
| 02/22/2013 9:36AM |
The FORMAT() function is available from version 2012 onwards. Once upgraded, you can use
select FORMAT(@date,'MM/dd/yyyy hh:mm:s tt')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With