Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Making DATEDIFF "flexible"

    I have the following SQL statement that works out how many days are between todays date and the first of April 2007.
    Code:
    SELECT	DATEDIFF(dd,'2007-04-01 00:00:00.000',GetDate()) AS 'Days Left'
    This works fine, however: After the 1st April 2007 I want to start counting down the days till 1st April 2008 - and so on and so forth.

    How can I do this? Hopefully you understand my question - if not, ask me any questions needed!

    Cheers - GeorgeV
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    Hope I'm not putting my foot in my mouth here, I'm just learning SQL:

    SELECT DATEDIFF (dd,'2007-04-' & Year(GetDate)+1 ,GetDate())AS 'Days_Left';

    I don't know if you can perform that calculation in the middle there like that. Is there a way you can perform the calculation before the select statement and load it into a variable? I haven't yet seen variable in SQL so I don't know if that can be done.

    EDIT: I realized concatenate was needed
    Last edited by starkmann; 02-26-07 at 09:34.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have tried things like:

    SELECT DATEDIFF(dd, YEAR(GetDate())&'-04-01 00:00:00.000',GetDate()) AS 'Days Left'

    But you get the error message:

    Server: Msg 245, Level 16, State 1, Line 9
    Syntax error converting the varchar value '-04-01 00:00:00.000' to a column of data type int.

    --

    EDIT: Variables appear to be working!
    Code:
    DECLARE	@Year 	AS VarChar(30)		SET @Year = YEAR(GetDate())
    SELECT	DATEDIFF(dd, @Year + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'
    Last edited by gvee; 02-26-07 at 09:48.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    suck, I hoped that would do it.
    maybe a case statement? it's not pretty but if you did cases through 2025, you should be ok, I would think.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here's how to do it without the variable

    SELECT DATEDIFF(dd, cast(Year(getdate()) as char(4)) + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aye RNG, if you wrap ABs round it (or swap the cast statement with the GetDate()

    Ok, know to make it harder...

    Say this was the 2nd of April I'd want to get a result of:
    0 years, 11 months, 29 days.

    (if you get me )
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by starkmann
    suck, I hoped that would do it.
    maybe a case statement? it's not pretty but if you did cases through 2025, you should be ok, I would think.
    It did, it did, it did!
    I musta been editing it as you posted

    Cheers stark
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'm in a particularly evil mood today. I'll give you a solution, then let you sus out which line actually solves your problem and maybe even work out how the demo statement does what it does!
    Code:
    SELECT Convert(CHAR(10), d, 121)
    ,  DateDiff(day, d, Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
       FROM (SELECT DateAdd(day, z31.d + z32.d + z33.d + z43.d + z53.d + z63.d, GetDate()) AS d
          FROM (SELECT       0 AS d UNION SELECT   1 UNION SELECT   2) AS z31
          CROSS JOIN (SELECT 0 AS d UNION SELECT   3 UNION SELECT   6) AS z32
          CROSS JOIN (SELECT 0 AS d UNION SELECT   9 UNION SELECT  18) AS z33
          CROSS JOIN (SELECT 0 AS d UNION SELECT  27 UNION SELECT  54) AS z43
          CROSS JOIN (SELECT 0 AS d UNION SELECT  81 UNION SELECT 162) AS z53
          CROSS JOIN (SELECT 0 AS d UNION SELECT 243 UNION SELECT 486) AS z63) AS z
       ORDER BY d
    -PatP

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok Pat, I accept your challenge.
    However it will have to wait till tomorrow morning at workies!

    Never heard of a CROSS join before *ponders*
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh, I think you'll have fun working this one out... The puzzle is one that is tough for most folks to get their head around at first, but then a wonderful thing once they "grok" it. The neat thing about a simple statment like this is that you can demonstrate that it works (just run the silly thing), then you can sit down and tear it apart to see the inner workings.

    I sometimes throw these out for the kids with assignments... They can see that they've got the solution, but by the time they understand it well enough to turn it in, they've already learned a LOT more about SQL than it would have taken to just do their homework and be done with it. The neat thing about these is that for the business user who only needs a solution, they are sufficient. For the serious SQL user, they are a chance to learn new things. For the student looking for someone to do their homework, they are just plain useless.

    Our beloved R937 coined a name for these kind of solutions, an NZDF or "Non-Zero Deviousity Factor" solution. Every so often I enjoy creating one, although I almost always use them for cases where I'm not sure about the business need, and in your case I'll take that as a given... I just thought you'd enjoy the puzzle.

    -PatP

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I bet I will
    I already figured a small part of it out before I left the office at 5:30.
    I always prefer the challenge - hate answers on a plate (unless I've been hitting my head against a brick wall for days).

    This problem is not something I need the answer to, just something I want - and I'm sure your query will help me learn the trickery I need

    I get like that sometimes - always wanting to go above and beyond a probelm just to learn (I think that's what helped me land this job )

    Cheers again!
    George
    Home | Blog

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by georgev
    I get like that sometimes - always wanting to go above and beyond a probelm just to learn (I think that's what helped me land this job )
    Oh, to be young and not yet jaded...
    Inspiration Through Fermentation

  13. #13
    Join Date
    Feb 2007
    Posts
    348
    youch, I took a shot at this.
    I don't think I got it either, all I got was
    todays date as milliseconds, the number of days between today and April 1, 2007. I'm anxious to see George's answer and then the actual answer.

    Thanks for the exercise.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    poor man's tally table

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Convert(CHAR(10), d, 121)
    ,  DateDiff(day, d, Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
       FROM (SELECT DateAdd(day, z31.d + z32.d + z33.d + z43.d + z53.d + z63.d, GetDate()) AS d
          FROM (SELECT       0 AS d UNION SELECT   1 UNION SELECT   2) AS z31
          CROSS JOIN (SELECT 0 AS d UNION SELECT   3 UNION SELECT   6) AS z32
          CROSS JOIN (SELECT 0 AS d UNION SELECT   9 UNION SELECT  18) AS z33
          CROSS JOIN (SELECT 0 AS d UNION SELECT  27 UNION SELECT  54) AS z43
          CROSS JOIN (SELECT 0 AS d UNION SELECT  81 UNION SELECT 162) AS z53
          CROSS JOIN (SELECT 0 AS d UNION SELECT 243 UNION SELECT 486) AS z63) AS z
       ORDER BY d
    121 = yyyy-mm-dd hh:mi:ss.mmm(24h)
    0 = mon dd yyyy hh:miAM (or PM)
    3 = dd/mm/yy
    6 = dd mon yy
    9 = mon dd yyyy hh:mi:ss:mmmAM (or PM)
    ...
    Am I on the right track?

    EDIT: Re-read, ignore the above (bar 121) because it's carp
    EDIT: I'd like to point out that the above was assumed without running the code
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •