CODEWITHSUNDEEP
mysqlsql
Kalenji
Kalenji

Reputation: 407

SQL Dates difference in number of days

I try to get results in number of days between the issue.date and today's date. My SQL gives me format dd.mm.YYYY as int[11] and I convert it to varchar [11] as the first format gives time since zero when exported to csv or xls.

So, my question is how to change int / varchar to date format to calculate difference between that date and today. Today's date is in datetime format.

Below is used to convert

CONVERT (VARCHAR(11),DATEADD(day,issue_date,'1971/12/31'),106) AS IssueDate

Format is dd mm YYYY.

I use CONVERT(VARCHAR(11), GETDATE(), 106) to get the same format but I do not know how to change to date format.

Note: I can not create any variables in the environment I work.

Upvotes: 0

Views: 5522

Answers (3)

Faraz Babakhel
Faraz Babakhel

Reputation: 664

The DATEDIFF() function is use to find Difference between two dates. The DATEDIFF() has three parameters as list below:

1 :- DATAPART (DAY,HOURS,MONTH,YEARS,SECONDS etc)

2 :- START DATE

3 :- END DATE

Example:

SELECT DATEDIFF(DAY , '2017-12-10',GETDATE()) // Result should be 151

Upvotes: 1

Kalenji
Kalenji

Reputation: 407

The answer is rather easy:

CONVERT (date,DATEADD(day,wo_header.issue_date,'1971/12/31'),106)

and 

CONVERT(date, GETDATE(), 106)

and then (DATEDIFF (day,CONVERT(date, GETDATE(), 106), CONVERT (date,DATEADD(day,issue_date,'1971/12/31'),106) )) AS 'Days open'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your question just doesn't fully make sense. You should be storing date/times using native formats, not strings. Not numbers. Period. That is the right way to store the date/time values. Then you use date/time functions (such as datediff()) on the proper types.

When you do, you can just use datediff(day, issue_date, getdate()).

If for some reason you have dates in the format DD.MM.YYYY, you can use convert(date, ddmmyy, 104). Supported types are in the documentation.

Upvotes: 1

Related Questions