CODEWITHSUNDEEP
sqlsql-server
blue18hutthutt
blue18hutthutt

Reputation: 3243

How do you parse a custom formatted date time string into a datetime?

This is for Microsoft SQL Server

I have an audit table with a timestamp represented as a string - timestamps are in multiple locale-specific representations (eg some are in mm/dd others are dd/mm)

I know some rows that I'm interested in have a timestamp string in the format of dd/MM/yy HH:mm:ss

I want to write a query that will return rows where the timestamp string is NOT in that format so I imagine something like this (with an imaginary PARSEDATE function)

WHERE PARSEDATE(timestamp) IS NOT NULL

Everything I've read about T-SQL datetime functions seem to involve well defined format codes eg 112 but I don't see a generalized way of being able to provide a custom date time format string for parsing?

Upvotes: 0

Views: 717

Answers (1)

evhen14
evhen14

Reputation: 1927

Set the format before running your query.

SET LANGUAGE us_english;
SET DATEFORMAT dmy;

In your query 

WHERE ISDATE(timestamp) = 1

More information can be found here

Upvotes: 1

Related Questions