1

I am being requested by a vendor to convert a SQL Server DateTime value into the following specific timestamp format %Y-%m-%d %H:%M:%OS4 %z. The query will be placed into their software to query my database.

The desired output seems like it might be vaguely related to this posixct question in R; but this is about output in SQL Server. I have been provided no specific errors other than the field is appearing in their software as blank but it works in my SQL interface.

The output I am getting from SQL is like the following:

2016-03-16 00:00:00.000

The output of another column that comes like this they have no problem varchar(30) with 2016-03-16 11:34:36 as it just treats it as a string WHEN mapped into a string field.. but for it to treat it needs to be in the referenced %Y-%m-%d %H:%M:%OS4 %z format.

The differences seem subtle other than the data type but even when mapped into a string field it is failing to map the SQL native output into once of its fields.

I have tried for example 

cast(convert(datetime, tn.last_settled, 126) as Varchar(30))

on the datetime but that results in Jan 1 1990 12:00AM and not the desired format.

How do I get to the desired string 2016-03-16 11:34:36 and datetime 2016-03-16 00:00:00.000 formats into the required %Y-%m-%d %H:%M:%OS4 %z format from a SQL Server datetime format in T-SQL?

Community
  • 1
  • 1
CRSouser
  • 658
  • 9
  • 25
  • It sounds like they've written their application to parse a string instead of getting working with datetimes (perhaps they're coming from a database that didn't handle datetimes natively). What version of SQL Server are you using? – lc. Mar 17 '16 at 00:32
  • @lc. I am running SQL 2012 – CRSouser Mar 17 '16 at 00:32

1 Answers1

4

For SQL Server 2012 and above, you should be able to use FORMAT with a standard .NET formatting string to give them the output they need. For example:

FORMAT(tn.last_settled, 'yyyy-MM-dd HH-mm:ss.ffff')

To turn your string input into a datetime, you should be able to use PARSE:

PARSE(@theStringValueTheySendYou as datetime)
lc.
  • 113,939
  • 20
  • 158
  • 187
  • If I lower case the year (yyyy vs. YYYY) that worked for the datetime, but not the string. I had to do a convert on the string paired with that to get the second part to work. `FORMAT(convert(datetime,tk.last_start_date,126), 'yyyy-MM-dd HH-mm:ss.ffff') ` – CRSouser Mar 17 '16 at 00:49
  • @CRSouser Sorry yes, lowercase y – lc. Mar 17 '16 at 00:55