[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL DateDiff  String issue

Posted on 2012-09-21
Medium Priority
499 Views
Hello experts I have a desire to pull the years, months and days of a string value. I'm wanting to go from:

'20050704' to '7 years 2 months 17 days'

To accomplish this I currently have:

select DATEDIFF(year, CAST(CAST(20050704 AS VARCHAR(8)) AS DATETIME), GETDATE())

select DATEDIFF(month, CAST(CAST(20050704 AS VARCHAR(8)) AS DATETIME), GETDATE())

select DATEDIFF(day, CAST(CAST(20050704 AS VARCHAR(8)) AS DATETIME), GETDATE())

I'm trying to figure out how to calculate the correct values to '7 years 2 months 17 days'
0
Question by:robthomas09
• 3
• 2

LVL 8

Expert Comment

ID: 38423055
DECLARE @dt VARCHAR(8)
SET @dt = '20050704'

--//To accomplish this I currently have:

select DATEDIFF(year, CAST(CAST(@dt AS VARCHAR(8)) AS DATETIME), GETDATE())

select month(GETDATE()) - month(CAST(CAST(@dt AS VARCHAR(8)) AS DATETIME))

select DAY(GETDATE()) - DAY(CAST(CAST(@dt AS VARCHAR(8)) AS DATETIME))
0

LVL 8

Accepted Solution

Crashman earned 2000 total points
ID: 38423072
ummm, here is....

Here
0

Author Comment

ID: 38423083
@Crashman, the only thing that does not work properly with that code is that if the date I'm searching compared to today is say '19970727' I get a negative days number.
0

LVL 8

Expert Comment

ID: 38423112
select ABS(DAY(GETDATE()) - DAY(CAST(CAST(@dt AS VARCHAR(8)) AS DATETIME)))
0

LVL 70

Expert Comment

ID: 38423349
The hyperlinked code has at least one bug in it that I see immediately.

DECLARE @end_date char(8)
SET @end_date = CONVERT(char(8), GETDATE(), 112)

SELECT
start_date,
@end_date AS end_date,
CAST(
DATEDIFF(YEAR, start_date, @end_date) - CASE WHEN SUBSTRING(start_date, 5, 4) > SUBSTRING(@end_date, 5, 4) THEN 1 ELSE 0 END
AS varchar(4)) + ' years ' +
CAST(
CASE WHEN SUBSTRING(start_date, 5, 4) > SUBSTRING(@end_date, 5, 4)
THEN 12 + CAST(SUBSTRING(@end_date, 5, 2) AS int) - CAST(SUBSTRING(start_date, 5, 2) AS int)
ELSE DATEDIFF(MONTH, start_date, @end_date) % 12 END
- CASE WHEN RIGHT(start_date, 2) > RIGHT(@end_date, 2) THEN 1 ELSE 0 END
AS varchar(2)) + ' months ' +
CAST(
CASE WHEN RIGHT(start_date, 2) > RIGHT(@end_date, 2)
THEN DATEDIFF(DAY, STUFF(CONVERT(char(8), DATEADD(MONTH, -1, @end_date), 112), 7, 2, SUBSTRING(start_date, 7, 2)), @end_date)
ELSE CAST(RIGHT(@end_date, 2) AS int) - CAST(RIGHT(start_date, 2) AS int)
END
AS varchar(2)) + ' days '
FROM (
SELECT '20050704' AS start_date UNION ALL --NOTE: char(8), NOT datetime
SELECT '20050922' AS start_date UNION ALL
SELECT '20051008' AS start_date UNION ALL
SELECT '20051022' AS start_date
) AS start_dates
0

LVL 70

Expert Comment

ID: 38423371
Wish I had known you were in a rush to close, wouldn't have spent the time :-) ...
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month19 days, 2 hours left to enroll