[Resolved] MSSQL Customize Date & Time Format

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 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?

Quote:

---

Originally Posted by 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:

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

---

Quote:

---

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.

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

---

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:

---

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))

Quote:

---

Originally Posted by brucevde

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.