Hello Gurus...
What is sql statement to get date & time format as below in MSSQL?
Format like this ==> "dd/mm/yyyy hh:mm:ss PM"
Example like this ==> "28/10/2009 02:09:45 PM"
Thanks
Printable View
Hello Gurus...
What is sql statement to get date & time format as below in MSSQL?
Format like this ==> "dd/mm/yyyy hh:mm:ss PM"
Example like this ==> "28/10/2009 02:09:45 PM"
Thanks
The easiest way to make formatted Strings from Dates is to use CONVERT
Unfortunately it doesn't have direct support the exact format you want, so in this case you need to do it in two parts, eg:
Code:SELECT CONVERT (char(10), DateField, 103) + ' ' + CONVERT (char(10), DateField, 8)
FROM tablename
I found SQL Statement as below. But the result "2009/10/28 02:09:45 PM".
How to modified this SQL Statement in order to get the result like this "28/10/2009 02:09:45 PM".
Anybody?
Code:use vtcs select top (1000)
CAST(DATEPART(YYYY,tbl_event.msg_time) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,tbl_event.msg_time) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,tbl_event.msg_time) AS CHAR(3)),2) + ' '
+ CASE WHEN DATEPART(HH,tbl_event.msg_time) < 13
THEN RIGHT(CAST(100+DATEPART(HH,tbl_event.msg_time) AS CHAR(3)),2)
ELSE CAST(DATEPART(HH,tbl_event.msg_time)-12 AS CHAR(2))
END + ':'
+ RIGHT(CAST(100+DATEPART(MI,tbl_event.msg_time) AS CHAR(3)),2)
+ CASE WHEN DATEPART(HH,tbl_event.msg_time) < 13
THEN 'AM'
ELSE 'PM'
END
From tbl_event
order by tbl_event.msg_time desc
Ah, there are some Convert styles which give AM/PM, but that isn't one of them... unfortunately the only ones that have it also have milliseconds.Quote:
I got this as result ==> 12/11/2009 23:42:51
As such, the best thing to do is use the first Convert from my post to get the Date format you want, and use the relevant parts of the code you found (from CASE WHEN DATEPART onwards) for the Time part.
It is rather worrying that you can't work that much out by yourself - because it should be clear what the YYYY and DD and MM refer to.Quote:
I found SQL Statement as below. But the result "2009/10/28 02:09:45 PM".
How to modified this SQL Statement in order to get the result like this "28/10/2009 02:09:45 PM".
Select Convert(varchar, GetDate(),103) + ' ' + LTrim(Substring(Convert(varchar, GetDate(),109),13,8) + ' ' + Right(Convert(varchar, GetDate(),109),2))Quote:
What is sql statement to get date & time format as below in MSSQL?
Format like this ==> "dd/mm/yyyy hh:mm:ss PM"