Parse a Date from Unformatted Text in SQL

Parse a date from unformatted text in SQL

This is already a valid date - ISO-8601 format - just use:

SELECT CAST('20060508' AS DATETIME)

or alternatively:

SELECT CONVERT(DATETIME, '20060508', 112)

and that should do just fine!

In order to get your "May 08, 2006" display, do another convert into varchar, using the date convert style 107:

SELECT CONVERT(VARCHAR(25), CAST('2006-05-08' AS DATETIME), 107)

See here for more information on casting & converting in MS SQL

How to convert text column to datetime in SQL

Use convert with style 101.

select convert(datetime, Remarks, 101)

If your column is really text you need to convert to varchar before converting to datetime

select convert(datetime, convert(varchar(30), Remarks), 101)

Convert string to date in specific format

You need the convert function, where you can specify a format code:

select convert(datetime, '01/08/2014', 103)

The 103 means dd/mm/yyyy. See the docs.

Parse string to DateTime in C# mimicking behavior of SQL Server CAST/CONVERT

Update: @jeroen-mostert's comment about using SqlDateTime.Parse is probably a better answer than what what we wound up going with.

Original answer:

In case it helps anyone in the future, the answer wound up being to use DateTime.TryParse() and, if that failed, calling DateTime.TryParseExact() with the special formats not already supported by TryParse().

From glancing over the source code it seems that Parse/TryParse is more lenient about things like separators, such that it works even with similar formats that are not shown when enumerating dateTimeFormatInfo.GetAllDateTimePatterns(). I believe that explains why TryParseExact didn't seem to work with formats that TryParse supported, even when TryParseExact was using all the DateTime patterns provided by the DateTimeFormatInfo object.

I did also validate format support against the listed Date and Time Styles in Microsoft's docs. This solution doesn't support 100% of them, though. That is by design since some combinations can be non-deterministic, and we are thankfully confined to a specific culture.

Not a perfect solution, as pointed out in the comments, but it did the trick here.

SSIS - Converting unformatted strings dates to YYYY-MM-DD

This task is quite tricky since we're not only dealing with unformatted String dates but they are also not in the region netural YYYY-MM-DD format.

To fix this, we can use the follwing expression for the Derived Column:

RIGHT([mydate],4) + "/" + SUBSTRING([mydate],FINDSTRING([mydate],"/",1) + 1,FINDSTRING([mydate],"/",2) - FINDSTRING([mydate],"/",1) - 1) + "/" + SUBSTRING([mydate],1,FINDSTRING([mydate],"/",1) - 1)

The expression is quite long but what it is doing is taking the values (month, day, year) between the forward slashes / and concatenating them into a format that resembles YYYY/MM/DD that can then be converted in SSIS using a Data Conversion transformation. This avoids the error of dealing with the change in length in dates like 1/1/2000 and 10/10/2000.

The output of the derived column was named YYYYMMDD and this value was then passed into a Data Conversion transformation that has output Date Converted YYYYMMDD as seen below.

The Data Conversion task is simply doing the follwing:

---

Related Topics