[Resolved] MSSQL Customize Date & Time Format

[g-mie]

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

[si_the_geek]

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:

SELECT CONVERT (char(10), DateField, 103) + ' ' + CONVERT (char(10), DateField, 8)
FROM tablename

[g-mie]

I got this as result ==> 12/11/2009 23:42:51

and

I am hoping to have this ==> 12/11/2009 11:42:51 PM

Any other suggestion si_the_geek?

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:

SELECT CONVERT (char(10), DateField, 103) + ' ' + CONVERT (char(10), DateField, 8)
FROM tablename

[g-mie]

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?

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

[si_the_geek]

I got this as result ==> 12/11/2009 23:42:51

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.

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.

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".

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.

[brucevde]

What is sql statement to get date & time format as below in MSSQL?
Format like this ==> "dd/mm/yyyy hh:mm:ss PM"

Select Convert(varchar, GetDate(),103) + ' ' + LTrim(Substring(Convert(varchar, GetDate(),109),13,8) + ' ' + Right(Convert(varchar, GetDate(),109),2))

[g-mie]

Select Convert(varchar, GetDate(),103) + ' ' + LTrim(Substring(Convert(varchar, GetDate(),109),13,8) + ' ' + Right(Convert(varchar, GetDate(),109),2))

Thanks brucevde. It's work

Thanks also to everybody because helping me.