October 3, 2011 at 6:41 am
Hi,
Is there a way to get time format like yyyy-mm-dd hh:mi:ss AM/PM in SQL 2005? The closest I could get was convert(varchar,getdate(),121); but it didnt have AM/PM.
Any ideas to resolve this is welcome
October 3, 2011 at 6:49 am
You can do it manually concatenating the single dateparts.
However, IMHO this kind of things does not belong to the database layer and should be coded on the application side.
-- Gianluca Sartori
October 3, 2011 at 6:57 am
If you insist doing it on the DB side, here's the code:
--yyyy-mm-dd hh:mi:ss AM/PM
DECLARE @dt datetime
SET @dt = GETDATE()
SELECT
CAST(YEAR(@dt) as char(4)) + '-' +
REPLACE(STR(MONTH(@dt),2),' ','0') + '-' +
REPLACE(STR(DAY(@dt),2),' ','0') + ' ' +
REPLACE(STR(CASE WHEN DATEPART(hour,@dt) > 12 THEN DATEPART(hour,@dt) - 12 ELSE DATEPART(hour,@dt) END,2),' ','0') + '-' +
REPLACE(STR(DATEPART(minute,@dt),2),' ','0') + ':' +
REPLACE(STR(DATEPART(second,@dt),2),' ','0') + ' ' +
CASE WHEN DATEPART(hour,@dt) > 12 THEN 'PM' ELSE 'AM' END
-- Gianluca Sartori
October 3, 2011 at 7:06 am
select convert(varchar,getdate(), 120) + ' ' + CASE WHEN DATEPART(hour,GETDATE()) > 12 THEN 'PM' ELSE 'AM' END
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 3, 2011 at 7:42 am
J Livingston SQL (10/3/2011)
select convert(varchar,getdate(), 120) + ' ' + CASE WHEN DATEPART(hour,GETDATE()) > 12 THEN 'PM' ELSE 'AM' END
Please note that the "hour" datepart does not get turned into 12 hours format.
In my timezone, now it's 15.41 and your code returns 2011-10-03 15:41:35 PM instead of 2011-10-03 03:40:35 PM.
-- Gianluca Sartori
October 3, 2011 at 8:21 am
Gianluca Sartori (10/3/2011)
J Livingston SQL (10/3/2011)
select convert(varchar,getdate(), 120) + ' ' + CASE WHEN DATEPART(hour,GETDATE()) > 12 THEN 'PM' ELSE 'AM' ENDPlease note that the "hour" datepart does not get turned into 12 hours format.
In my timezone, now it's 15.41 and your code returns 2011-10-03 15:41:35 PM instead of 2011-10-03 03:40:35 PM.
agreed....
therefore for my personal preference, I would remove leading zero in hours
DECLARE @dt datetime
SET @dt = GETDATE()
SELECT
CAST(YEAR(@dt) as char(4)) + '-' +
REPLACE(STR(MONTH(@dt),2),' ','0') + '-' +
REPLACE(STR(DAY(@dt),2),' ','0') + ' ' +
STR(CASE WHEN DATEPART(hour,@dt) > 12 THEN DATEPART(hour,@dt) - 12 ELSE DATEPART(hour,@dt) END,2) + ':' +
REPLACE(STR(DATEPART(minute,@dt),2),' ','0') + ':' +
REPLACE(STR(DATEPART(second,@dt),2),' ','0') + ' ' +
CASE WHEN DATEPART(hour,@dt) > 12 THEN 'PM' ELSE 'AM' END
it all depends, I suppose.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 4, 2011 at 12:03 am
By uisng the below string and conversion functions you can meets your requirements.
SELECT CONVERT(VARCHAR(30),GETDATE(),121) + ' ' + SUBSTRING( CONVERT(VARCHAR(30),GETDATE(),0),18,1000).
October 5, 2011 at 7:38 am
This might be safer:
SELECT CONVERT(VARCHAR(30),GETDATE(),121) + ' ' + REVERSE(SUBSTRING(REVERSE( CONVERT(NVARCHAR(30),GETDATE(),0)),1,2))
(when date goes to 2 digit day)
October 5, 2011 at 8:56 am
Almost. 😉 You also need to subtract 12 hours when the time is 1300 hrs or later. Similar to Gianlucas earlier solution.
SELECT
CONVERT(VARCHAR(30),
DATEADD(HH,
CASE WHEN DATEPART(HH,GETDATE())>=13 THEN -12 ELSE 0 END ,
GETDATE()),
121)
Just for the record, I don't reccomend doing this in the database either. 🙂
October 5, 2011 at 9:04 am
(Nigel) Yep, yep, yep 🙂
October 5, 2011 at 10:12 am
OK, here's another way:
http://spaghettidba.com/2011/10/05/formatting-dates-in-t-sql/
-- Gianluca Sartori
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply