asked on 11/30/2015

SQL Select - Text to Date Conversion,

I have a text column in my SQL server table that contains a date like this 02/21/2015. I need to evaluate this column in a where a clause so I can get all the records that have a date > 9/21/2015.

This is what I need to do:
Select CREATE_DATE from MyTable where CREATE_DATE > 09/21/2105.

I am assuming there is a CAST or CONVERT that I can use?

Microsoft SQL Server

Last Comment

PortletPaul

12/1/2015

ASKER CERTIFIED SOLUTION

jyothees venkat

11/30/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Brian Crowe

11/30/2015

You cannot convert directly from a TEXT datatype to DATE but you can convert it if you do it in two steps, convert to VARCHAR/NVARCHAR and then to DATE:

DECLARE @Example TABLE
(
	DateString	TEXT
);

INSERT INTO @Example (DateString)
VALUES ('09/21/2015');

SELECT CONVERT(DATE, CAST(DateString AS VARCHAR), 101)
FROM @Example

Open in new window

If you are using SQL 2012 or above then I would recommend using the TRY_CONVERT function instead.

Scott Pletcher

11/30/2015

To avoid errors from invalid data that does not contain a date, the safest way to do it is below. You will inevitably lose efficiency no matter how you do this conversion:

Select CREATE_DATE
from MyTable
where 1 =
case when isdate(left(create_date, 10)) = 0 then 0
when convert(date, left(create_date, 10)) > '20150921') then 1
else 0 end

Note that a character comparison against a 'mm/dd/yyyy' format will be invalid!

PortletPaul

12/1/2015

what date is 07/08/2015? (July or August?)

PLEASE stop treating mm/dd/yyyy as the ultimate and universal method of writing dates because it just isn't that.

Most of the world does NOT use that format and you cannot be certain your server will always recognize '09/21/2015' if it is expecting (for example) '21/09/2015'

nb: Scott is using the safest of all 'date literals' for SQL Server (YYYYMMDD) which will be recognized regardless of server settings

no points pl.