Time format

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


 

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

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

 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/

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

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.

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

wrong output

00000p

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?

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

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.

@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

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

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. 

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

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

@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

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

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.

@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