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

x
?
Solved

SQL DateDiff  String issue

Posted on 2012-09-21
6
Medium Priority
?
499 Views
Last Modified: 2012-09-21
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
Comment
Question by:robthomas09
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Crashman
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))

Open in new window

0
 
LVL 8

Accepted Solution

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

Here
0
 

Author Comment

by:robthomas09
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

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

Expert Comment

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

Please try this:


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

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question