April 13, 2009 at 11:13 am
I can today's date by getdate()
and how to find number days in one and half year considering this date?
April 13, 2009 at 11:21 am
1.5 years is 547 or 548 days, depending on how you want to count it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 13, 2009 at 12:14 pm
t-sql?
April 13, 2009 at 1:14 pm
did you test this ?
Select datediff(dd, dateadd(mm,-18,getdate(), getdate)
i.e. determine the number of days ( dd ) between the getdate minus 18 months (12+6) and the current date.
It will not hurt to read this article Lynn Pettis wrote :
http://www.sqlservercentral.com/articles/T-SQL/63351/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2009 at 1:21 pm
I'd go with ALZDBA's solution, but you should define what 1 year and a half means to you.
If it's early Sept and you go back a year and a half, do you go back to March, or Feb? Since there are less days, someone might think 18 months back to Mar or 547 days back to Feb.
April 13, 2009 at 1:28 pm
ALZDBA (4/13/2009)
did you test this ?
Select datediff(dd, dateadd(mm,-18,getdate(), getdate)i.e. determine the number of days ( dd ) between the getdate minus 18 months (12+6) and the current date.
It will not hurt to read this article Lynn Pettis wrote :
You were missing 2 parentheses. The corrected code is:
Select datediff(dd, dateadd(mm,-18,getdate()), getdate())
I guess I missed one thing at first. The answer will always be the same, except when there's a leap year between the 2 dates.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 13, 2009 at 11:59 pm
Indeed, must have been a dyping error 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2009 at 12:26 am
Steve Jones - Editor (4/13/2009)
I'd go with ALZDBA's solution, but you should define what 1 year and a half means to you.If it's early Sept and you go back a year and a half, do you go back to March, or Feb? Since there are less days, someone might think 18 months back to Mar or 547 days back to Feb.
I created a simple test using ALZDBA code on my Tally table.
select datediff(dd, dateadd(mm,-18 , N_Date), N_Date) , COUNT(*)
from dbo.Tally
WHERE N_DATE > '19010630' -- to prevent overflow for smalldatetime value
GROUP BY datediff(dd, dateadd(mm,-18 , N_Date), N_Date)
ORDER BY datediff(dd, dateadd(mm,-18 , N_Date), N_Date)
N_Date contains full range of dates fit to smalldatetime datatype.
Here is the outcome:
54610531
54715941
54813490
54918832
5505309
As you can see 18 month contain quite different number of days.
So, I have to support the request: Define "1 year and a half".
_____________
Code for TallyGenerator
April 14, 2009 at 1:55 am
FYI Lynn also posted a nice overview of common date routines.
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply