Time format

Question

select left(convert(varchar online_time109)4)+' '+ right(convert(varchar online_time109)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

Suthish Nair · Answer

I already given the solution... but you must do some more RnD for final o/p... SELECT REPLICATE('0'2-LEN(DATEPART(HHRIGHT(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(HHRIGHT(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(HHRIGHT(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 ranjan · Answer

@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_time109)2) from time again i hv problem of data in 24 hour format which i want to be in 12 hour format

Vulpes · Answer

What I had in mind was this: select left(convert(char(16) online_time)5) + ' ' + right(convert(varchar online_time109)2) but if Suthish's suggestion is working for your online_time I'd go with that.

nishant ranjan · Answer

@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 ranjan · Answer

@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 Nair · Answer

Yes Vulpes was right.. Edited and changed the datatype to char..

Vulpes · Answer

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 Vatsa · Answer

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 ranjan · Answer

@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 Vatsa · Answer

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 ranjan · Answer

@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 Vatsa · Answer

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.

Vulpes · Answer

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 ranjan · Answer

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 Vatsa · Answer

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 ranjan · Answer

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 Nair · Answer

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 ranjan · Answer

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

Vulpes · Answer

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_time109) 5 )+' '+ right(convert(varchar online_time109)2) as online_time from time