Time format

nishant ranjan

14y2.6k

select left(convert(varchar, online_time,109),4)+' '+ right(convert(varchar, online_time,109),2) as online_time from time

above is my query

i want output as
04:30 am 4:30 am will do(if initial 0 is removed no prob)
09:45 pm
11:15 am

my prob is that above query is giving right output for time before 10 am/pm but after 9:59 am/pm time is wrong i.e, it displays

11:3 pm instead of 11:30 pm
and give right for
4:40 pm

how i can do that

19 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suthish Nair14y ago
Accepted answer
I already given the solution... but you must do some more RnD for final o/p...

SELECT
REPLICATE('0',2-LEN(DATEPART(HH,RIGHT(CONVERT(CHAR(17),CONVERT(datetime, CAST('09:00:00' AS TIME), 100), 100),5)))) +
LTRIM(RIGHT(CONVERT(CHAR(17),CONVERT(datetime, CAST('09:00:00' AS TIME), 100), 100),5))

SELECT
REPLICATE('0',2-LEN(DATEPART(HH,RIGHT(CONVERT(CHAR(17),CONVERT(datetime, CAST('23:05:00' AS TIME), 100), 100),5)))) +
LTRIM(RIGHT(CONVERT(CHAR(17),CONVERT(datetime, CAST('23:05:00' AS TIME), 100), 100),5))

SELECT
REPLICATE('0',2-LEN(DATEPART(HH,RIGHT(CONVERT(CHAR(17),CONVERT(datetime, CAST('13:30:00' AS TIME), 100), 100),5)))) +
LTRIM(RIGHT(CONVERT(CHAR(17),CONVERT(datetime, CAST('13:30:00' AS TIME), 100), 100),5))
nishant ranjan14y ago
@Vulpes
yup i am doing with Suthish's suggestion

as per your last suggestion

select left(convert(char(16), online_time),5) + ' ' + right(convert(varchar, online_time,109),2) from time

again i hv problem of data in 24 hour format which i want to be in 12 hour format
Vulpes14y ago
What I had in mind was this:

select left(convert(char(16), online_time),5) + ' ' + right(convert(varchar, online_time,109),2)

but, if Suthish's suggestion is working for your online_time, I'd go with that.
nishant ranjan14y ago
@Abhimanyu

getdate() is working for me too...
but online_time is not working i.e., when i replace getdate with online_time its not wrking

note :- online_time is column in "time" table of datatype time(7)
nishant ranjan14y ago
@Suthis Nair
thanks replicate function is working

@Vulpes & @Suthis Nair

about changing to char, if u r talking abt below code than its nt wrking

SELECT left(CONVERT(char(5),'09:00:00',109),5)
SELECT left(CONVERT(char(5),'23:05:00',109),5)
SELECT left(CONVERT(char(5),'13:30:00',109),5)

nt converting to 12 hr fmt & nt evn in 108 format
Suthish Nair14y ago
Yes, Vulpes was right.. Edited and changed the datatype to char..
Vulpes14y ago
The time(7) datatype has a length of 16 characters including the fractional seconds part.

Rather than converting to varchar, I'd try converting to the fixed size type char(16) to see whether that will preserve any leading zero.
Abhimanyu K Vatsa14y ago

Look at image, that's working on my system. If you are still getting error, undoubtedly there is some issue with your "online_time".

Can you check using GETDATE() instead of "online_time"?
nishant ranjan14y ago
@Abhimanyu
still there is problem....
SELECT
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 12, 6) + '' +
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 25, 2) time

where time is my table name
i even tried with abc, online_time etc in place of time

if i dont give table name anywhere then from which tble it will retrieve online_time column
Abhimanyu K Vatsa14y ago
look at my reply carefully friend, you forgot to mention column name.

SELECT
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 12, 6) + '' +
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 25, 2) TIME
GO

Look the bold and underlined word, you need to write any word there to represent.

I am already using this on my system.
nishant ranjan14y ago
@Vulpes
yup thats exactly the problem is... its not reading initial 0
online_time time(7)

@Abhimanyu
SELECT
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 12, 6) + '' +
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 25, 2)

giving error invalid column name
Abhimanyu K Vatsa14y ago
don't use 'from time' at the end. Just use

SELECT
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 12, 6) + '' +
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 25, 2) TIME
GO

'TIME' is just a name, not any column from table and it can be anything.

Please check.
Vulpes14y ago
The problem seems to be that, if the time is before 10:00, it loses the initial zero when converting to varchar. Consequently, when you take the 5 leftmost characters you get stuff like 9:00:

What is the actual type of online_time?
nishant ranjan14y ago
SELECT
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 12, 6) + '' +
SUBSTRING(CONVERT(CHAR(26), online_time, 9), 25, 2) from time

wrong output

00000p
Abhimanyu K Vatsa14y ago
SELECT
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6)
GO

--OUTPUT = 11:30

and if you want to add AM or PM then

SELECT
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6) + '' +
SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 25, 2) COLNAME
GO

--OUTPUT = 11:30AM

at the place of '' you can add spaces or any separator.
nishant ranjan14y ago
well.. in this i have problem that i want to show time in 12 hour format.... not in 24 hour format. using 108 i cant display 11:30 PM ... it displays 23:30 PM
Suthish Nair14y ago
try..

SELECT CONVERT(varchar(5),right('01/01/2012 11:30', 5),108)
SELECT CONVERT(varchar(5),GETDATE(),114) SELECT CONVERT(varchar(5),GETDATE(),114)

Edited:

SELECT CONVERT(varchar(5),left('09:00:00', 5),108)
SELECT CONVERT(varchar(5),left('23:05:00', 5),108)
SELECT CONVERT(varchar(5),left('13:30:00', 5),108)

http://www.sqlusa.com/bestpractices/datetimeconversion/
nishant ranjan14y ago
well.. this doesnot solve my problem
if time is 9:00:00 in datebase then output is 9:00: am

in my database time is stored in this format
09:00:00
23:05:00
13:30:00

time format is wrong for time before 10 am
Vulpes14y ago
Off the top of my head, I'd have thought you should be getting the leftmost 5 rather than 4 characters as the ':' will count as well:

select left(convert(varchar, online_time,109),5)+' '+ right(convert(varchar, online_time,109),2) as online_time from time