EndTime columns in my SQL Server table which show as
I am looking to convert it to
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.
What's your timezone? You can use
IsDate() to see if
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?
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.
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,
You can use format function if your values are of datetime datatype.
FORMAT(StartTime, 'hhmm') AS Start_Time, FORMAT(EndTime, 'hhmm') AS End_Time