I am facing a problem in conversion and type cast. I've a field which is of Data Type BigInt that has a value stored in the format "yyyymmddhhmmss" like "20170609043000". I am trying to get the substring of date part in the timestamp like 20170609 with a separator as 2017-06-09. Not able to fetch the date part in the timestamp.
Query I tried to fetch the Date part:
SELECT CONVERT(date,SUBSTRING(CAST(STR(evt.StartDate,15) as varchar),1, 5), 102) from Event evt
SELECT DATEADD(hour,-5, CONVERT(datetime, SUBSTRING(CAST(STR(evt.StartDate, 15) as varchar),6, 2) + '-' + SUBSTRING(CAST(STR(evt.StartDate, 15) as varchar), 8, 2) + '-' + SUBSTRING(CAST(STR(evt.StartDate, 15) as varchar), 1,5) + ' ' + SUBSTRING(CAST(STR(evt.StartDate, 15) as varchar),10, 2) + ':' +SUBSTRING(CAST(STR(evt.StartDate, 15) as varchar), 12, 2) + ':' + SUBSTRING(CAST(STR(evt.StartDate, 15) as varchar), 14, 2), 120)) from Event evt
The first query returns the converted date but the month and date remains same , concatenation doesn't work if I try to substring month and date.
Second query doesn't work at all.
Any help would be great.
Why not just take the 8 left most characters, and convert it?
SELECT CONVERT(date, LEFT(20170609043000,8));
Ideally though, you should be using a date and time datatype to store your date and time data. Storing them in a different data type only ends up causing problems and never solves a problem that can't be solved else where (aka your presentation layer).