CODEWITHSUNDEEP
sqlsql-server-2008
Ganeshgm7
Ganeshgm7

Reputation: 455

How to use DATEDIFF for 2 strings in SQL Server?

I have 2 columns in a table with a datatype of varchar. But I have stored the values as dates like 14/10/2016. Now I need find the month differents for those 2 columns. I am not able to use the DATEDIFF for this because column is declared as varchar. Is there is any way to find the difference?

Upvotes: 1

Views: 10065

Answers (3)

Alex Wilkes
Alex Wilkes

Reputation: 95

You could use the 'CAST' or 'CONVERT' function so that SQL interprets the data is the data-type you require.

CAST:

 SELECT CAST(GETDATE() AS DATE) -- 2017-05-11

CONVERT:

 CONVERT(VARCHAR(10),GETDATE(),110) -- 11-05-2017

Replace the GETDATE() part with your Date Field. Once you have this working, you can then add this to your DATEDIFF function to convert the varchar field to DATE and then calculate the difference you're after.

There are many variations of the formatting within CONVERT, the '105' represents what format. You can find a list of the formats here:

https://www.w3schools.com/sql/func_convert.asp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

First, fix your data structure. In the short term, you can just add correct columns as computed columns:

alter table t realdate_col1 as (convert(date, col1, 103));
alter table t realdate_col2 as (convert(date, col2, 103));

You should be using the proper types.

In any case, the convert() explains what to do:

select datediff(day, convert(date, col1, 103), convert(date, col2, 103))

Or, go through this process to fix the data:

update t
    set col1 = convert(varchar(10), convert(date, col1, 103), 120),
        col2 = convert(varchar(10), convert(date, col2, 103), 120);

Then:

alter table t alter column col1 date;
alter table t alter column col2 date;

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

Use Conversion method and calculate difference :

  SELECT DATEDIFF(DAY,CONVERT(DATETIME,'14/10/2016',103),CONVERT(DATETIME,'15/10/2016',103))

Upvotes: 1

Related Questions