Questions › date conversion from hhmmss to hhmm returns error in sql

I have StartTime and EndTime columns in my SQL Server table which show as hhmmss (eg: 090000)

I am looking to convert it to 9:30 using

 CONVERT(varchar(5), StartTime, 108) AS Start_Time, 
 CONVERT(varchar(5), EndTime, 108) AS End_Time

but I get an error:

Conversion of varchar datatype to datetime datatype resulted in out-of-range value.

Any inputs?

Comments :
JonH replied

What's your timezone? You can use IsDate() to see if StartTime or EndTime are not valid dates as well. What are the datatypes of starttime and endtime?

I don't currently see how that code can return that error message. You appear to be converting FROM a datetime data type TO a varchar(5), yet the error message says the opposite. Are you sure you're not trying to insert these values into a datetime field?

Sean Lange replied

You really should leave formatting to the front end and return your data as the native type. I hope you are using the time datatype but I have a feeling you are not.

@Jon datatype is datetime

That's not what your error message says - there must be some misunderstanding.

2 Answers :
pedram answered

If your time column has datatype int then you should try like below,

DECLARE @yourtime AS INT = '093000'
SELECT CONVERT(VARCHAR(5),STUFF(STUFF(STUFF(@yourtime ,1 ,0 ,REPLICATE('0' ,6- LEN(@yourtime))),3,0,':'),6,0,':'),108) AS Start_Time,
CONVERT(VARCHAR(5),STUFF(STUFF(STUFF(@yourtime ,1 ,0 ,REPLICATE('0' ,6- LEN(@yourtime))),3,0,':'),6,0,':'),108) AS End_Time

OR if you want to set it as SSRS expression you should try below,


Faisal answered

You can use format function if your values are of datetime datatype.

FORMAT(StartTime, 'hhmm') AS Start_Time,
FORMAT(EndTime, 'hhmm') AS End_Time

Check example on SqlFiddle and read more about FORMAT.

Aruna Raghunam replied
Yes, datatype is datetime. Any idea how I can use the same in sql query?;
Faisal replied
I have added example and link to msdn in my answer.;