In celebration, I broke out the ginger tea and biscuits. The query wasn't pretty, and it had been a slog, but I had what I needed. There was also another issue - the time was wrong! This was puzzling until I realized that the result failed to account for the time zone in which I live (GMT -5, if you must know.) I decided to fix the column name and represent the value in local time: The time value was now human-readable, but I didn't like the column name, 2/NA. At last I was getting where I wanted to be: The CAST () Function In this example, we convert the string into a date data type (we specify this by using date as the second argument). The value '0001516993312125' is too long - we must truncate the leading zeroes and the last 3 digits:ĭATEADD(SS, CONVERT(BIGINT, SUBSTRING(JOIN_DATE, 4,10)), '19700101')ģ. The six functions are: CAST () CONVERT () PARSE () TRYCAST () TRYCONVERT () TRYPARSE () Below are example of how you can use these functions to convert a string to a date/time data type. You can, however, use DATEPART to take the various. The JOIN_DATE column is a string, so it must be converted to a numeric value.ĭATEADD(SS, CONVERT(BIGINT, JOIN_DATE), '19700101')Ģ. You can convert it to a DATETIMEOFFSET - a badly-named type that is really date+time+timezone: SELECT CAST ('T00:00:00+05:00' AS DATETIMEOFFSET) Unfortunately, if you cast that to a datetime, it just truncates the offset rather than incorporate it into the datetime.
However, these failures were instructive, as they led to the solution.ġ. I decided to convert the string to a date/time format that I was more used to seeing. To my dismay, the field's value was a string in Unix epoch time - a value representing the number of seconds since January 1, 1970. However, my first attempt failed miserably, as did my second and third attempts. Searching though a table in a SQL Server database, I found the date field I was looking for. I did a quick web search and learned that SQL Server's DATEADD() function was the ticket to what I needed. This was a nice, relatively pithy SQL query and it yielded: After much wailing and gnashing of teeth, I found the format I needed.
Searching though a table in a SQL Server database, I found the date field I was looking for.
0 kommentar(er)
