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,

=Format(Fields!StartTime.Value,"hh:mm")

=Format(Fields!EndTime.Value,"hh:mm")
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.;