CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 111

SQL Server : converting varchar field to date

I have in my table a varchar column with bunch of dates in the following format dd-MM-yyyy 

31-12-2018
01-01-2019
02-01-2019

I need to write a date based query that gets all the dates before 01-01-2019. I have tried using both CAST and CONVERT to convert these table values without luck.

Using CAST, my code is below:

SELECT
    CAST('''' + SUBSTRING(arc_billed_to_date, 4, 2) + '-' + SUBSTRING(arc_billed_to_date, 1, 2) + SUBSTRING(arc_billed_to_date, 6, 5)+ '''' AS date), 
    CAST('''' + SUBSTRING(arc_billed_to_date, 7, 5) + '-' + SUBSTRING(arc_billed_to_date, 1, 2) + '-' + SUBSTRING(arc_billed_to_date, 4, 2) + '''' AS date),
    CAST('''' + SUBSTRING(arc_billed_to_date, 7, 5) + '-' + SUBSTRING(arc_billed_to_date, 4, 2) + '-' + SUBSTRING(arc_billed_to_date, 1, 2) + '''' AS DATE), 
    CONVERT(DATE, '12-31-2018') AS x 
FROM
    wkpol

Using Convert

select Convert(datetime,'''' + SUBSTRING(arc_billed_to_date,7,5) + '-' + SUBSTRING(arc_billed_to_date,4,2) + '-' + SUBSTRING(arc_billed_to_date,1,2)+ '''',105) as x from wkpol

The error I get is

Conversion failed when converting date and/or time from character string.

Any help is appreciated.

Upvotes: 0

Views: 869

Answers (3)

zkemppel
zkemppel

Reputation: 238

SELECT  *
FROM wkpol
WHERE convert(date, arc_billed_to_date, 103) < convert(date, '01/01/2019');

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82010

In addition to Sean's comments, you can also set the DateFormat as DMY

Example

Declare @YourTable table (SomeCol varchar(50))
Insert Into @YourTable values
 ('31-12-2018')
,('01-01-2019')
,('02-01-2019')

Set DateFormat DMY

Select AsDate = try_convert(date,SomeCol)
 From  @YourTable

Returns

AsDate
2018-12-31
2019-01-01
2019-01-02

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23837

Well you are going to get many blames for using varchar field for date. Anyway, assuming it is a matter of another thread you can do the conversion like:

select * from myTable
where cast(right(arc_billed_to_date,4) + 
substring(arc_billed_to_date,4,2) +
left(arc_billed_to_date,2) as date) < '20190101';

You wouldn't be using any index either.

Upvotes: 2

Related Questions