Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xyz7767
Starting Member
4 Posts |
 Posted - 2013-12-18 : 00:31:47
|
| Hello gurus,I'm looking to extract the data from first day of year to last day of the previous month (e.g in Dec 2013 the data should be between 01-Jan-2013 to 30-Nov-2013). In Jan 2014, the data should be of whole 2013.Can you advice, how to use dynamic date funtionality for such scenario.Thanks in advance for any suggestion/help. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-18 : 01:04:40
|
[code]where datecol >= dateadd(year, datediff(year, 0, getdate()), 0)and datecol < dateadd(month, datediff(month, 0, getdate()), 0)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
xyz7767
Starting Member
4 Posts |
Posted - 2013-12-18 : 01:10:52
|
| The above code will not return data when month is January. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-18 : 01:18:45
|
quote:
Originally posted by xyz7767
The above code will not return data when month is January.
Based on your conditionquote:
I'm looking to extract the data from first day of year to last day of the previous month
You will not get any data as it is 2013-01-01 to 2012-12-31 ?So when the month is January, what is your expected date range ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-18 : 06:54:10
|
i think this is what you're looking at?where datecol >= dateadd(year, datediff(year, 0, dateadd(month, datediff(month, 0, getdate())-1, 0)), 0)and datecol < dateadd(month, datediff(month, 0, getdate()), 0) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
xyz7767
Starting Member
4 Posts |
Posted - 2013-12-18 : 12:16:06
|
| Many Thanks visakh16. Looks like your code woeked for this requirement. I'll some more testing on the same. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-19 : 03:34:49
|
| cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-12-24 : 07:46:30
|
| What if the current month is March?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Year to date functionality