CODEWITHSUNDEEP
sqlsql-serverdate
user2539602
user2539602

Reputation: 618

Convert String to date in select statement

I have a column which contains data but the problem is that this column has data type of varchar(50) and it has to be this due to some reasons,now what i want to do is while selecting data from table , i want to treat this column as date so that i can use it in where clause. i am using the code below for converting it yo date , but it converts some values and then gives an error this is my sample data

8/1/2002
6/9/2001
14/9/2001
26/7/2001
14/12/2001
21/1/2002
29/4/2001
7/5/2001
9/11/2001
16/7/2001


select CONVERT(date,sowingDate,103) from tblAgriculture_staging

I have tried which differnt version of date format e.g 103,105 etc

but still it converts some values but error comes on some values and query execution stops

Upvotes: 1

Views: 3551

Answers (4)

Andrey Morozov
Andrey Morozov

Reputation: 7979

Try this:

SET DATEFORMAT dmy;
select case when isdate(sowingDate) = 1 then CONVERT(date,sowingDate,103) end [date] from tblAgriculture_staging

or (if you are using sql 2012)

SET DATEFORMAT dmy;
select case when TRY_CONVERT(date, sowingDate) IS NOT NULL then CONVERT(date,sowingDate,103) end [date] from tblAgriculture_staging

but this solution hides (convert to NULL) all dates that are wrong. You can reverse the condition first and find/fix all rows with incorrect date (i.e. 31/02/2013) and then use this queries to show only valid dates

SQLFiddle

Upvotes: 1

John Wesley Gordon
John Wesley Gordon

Reputation: 910

This will return all the dates that are not actually dates.

select sowingDate from tblAgriculture_staging where isdate(sowingDate)=0

Upvotes: 0

Daniel E.
Daniel E.

Reputation: 2059

This is slightly crappy, but so is storing dates as varchar.

this is code that has worked for me in the past where i had some dates with 4 digit years and some with 2 digit years.

 where  (TRY_CONVERT(Datetime2,LTRIM(RTRIM([INVC DTE])),1)>=@From
            AND TRY_CONVERT(Datetime2,LTRIM(RTRIM([INVC DTE])),1)<=@To)
        OR (TRY_CONVERT(Datetime2,LTRIM(RTRIM([INVC DTE])),101)>=@From
            AND TRY_CONVERT(Datetime2,LTRIM(RTRIM([INVC DTE])),101)<=@To)

SQL Server 2012 + Only

This assumes you have cleaned up anything that actually just isn't a date...

Upvotes: 0

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

but it converts some values and then gives an error this is my sample data

because some data are in invalid format or contains incorrect symbols.
Try this: 

select CONVERT(date,ltrim(rtrim(sowingDate)), 103) from tblAgriculture_staging

or examine your values: 

select ISDATE(sowingDate) as IsDate, sowingDate, CASE WHEN ISDATE(sowingDate)=1 THEN CONVERT(date,ltrim(rtrim(sowingDate)), 103) ELSE NULL END from tblAgriculture_staging

Upvotes: 0

Related Questions