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.

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 - GeorgeVHope 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|||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!

DECLARE @.Year AS VarChar(30) SET @.Year = YEAR(GetDate())
SELECT DATEDIFF(dd, @.Year + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'|||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.|||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'|||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 :p)|||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|||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!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|||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*|||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|||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 :D

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!|||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...|||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.|||poor man's tally table ;)|||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 :p|||Am I on the right track?

don't think so. I gave you a little hint above btw.

try running the cross join stuff in exclusion of the rest, what does it do?|||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 :p
Hehe - I have no access to SS at home ;)|||I've got what the cross join select m'job does.

0 + 0 + 0 + 0 + 0 + 0 = 0
1 + 0 + 0 + 0 + 0 + 0 = 1
2 + 0 + 0 + 0 + 0 + 0 = 2
0 + 3 + 0 + 0 + 0 + 0 = 3
1 + 2 + 0 + 0 + 0 + 0 = 4
.. and so on

Correct?|||(Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')

And this bit gives me the correct year for the dateadd!

So adding days to the result of the above will give me the count...

Apart from when it is the 1st of April it says 366 instead of 0.

So now I want the query to return:
[Todays Date] [Days remaining]
Just a single line.
Possible?

EDIT: God this is hard to explain :p|||SELECT Convert(CHAR(10), GetDate(), 103) AS 'Todays Date',
DateDiff(day, GetDate(), Cast(Year(GetDate()) + CASE WHEN 3 < Month(GetDate()) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01') AS 'Days Remaining'

:D Fairly sure this is right now - just unsure what will happen when we get to April 1st or even - next year - I think it will be right.

EDIT:
Answer - if today was april 1st - displays 366 :(
I think it works right for next year though! :p|||There actually ARE 366 days between 2007-04-01 and 2008-04-01. I'll celebrate my first anniversary on DBForums in there!

-PatP|||There are?
surely there are only 365 BETWEEN in a leap year..?
On the day - the difference between days should read 0 - shouldn't it?|||By that reasoning, there should only be 364 if there isn't a leap year. Try executing:SELECT
DateDiff(day, '2006-04-01', '2007-04-01')
, DateDiff(day, '2007-04-01', '2008-04-01')-PatP|||I'll celebrate my first anniversary on DBForums in there!tee hee hee ;)

nice to see you back in form, old buddy