April 1, 2014 at 8:10 am
SQLRNNR (4/1/2014)
jcrawf02 (4/1/2014)
Sean Lange (3/31/2014)
Ed Wagner (3/31/2014)
rodjkidd (3/31/2014)
I do like paying it forward.Got an email from friend who was stuck on working out how many days old a debt is at the end of the month, and then flagging any that are 361 days or more.
To my trusty SSC briefcase and Lynn's common date routines, to work out the last day of the month... Because I don't remember them, just need to remember Lynn wrote an article on them! :w00t:
Cheers again to the forum and people taking the time to answer questions and post articles
Rodders...
I know this article well. It has come in handy many times.
+1
In fact, I have referred to that article so many times I just created myself a stored proc to return the info to me without having to load the page. 😀
create procedure [dbo].[CommonDateRoutines] as
select 'dateadd(dd, datediff(dd, 0, @ThisDate), 0)' as 'Beginning of this day'
, 'dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0)' as 'Beginning of next day'
, 'dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0)' as 'Beginning of previous day'
, 'dateadd(wk, datediff(wk, 0, @ThisDate), 0)' as 'Beginning of this week (Monday)'
, 'dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0)' as 'Beginning of next week (Monday)'
, 'dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0)' as 'Beginning of previous week (Monday)'
, 'dateadd(mm, datediff(mm, 0, @ThisDate), 0)' as 'Beginning of this month'
, 'dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)' as 'Beginning of next month'
, 'dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0)' as 'Beginning of previous month'
, 'dateadd(qq, datediff(qq, 0, @ThisDate), 0)' as 'Beginning of this quarter (Calendar)'
, 'dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0)' as 'Beginning of next quarter (Calendar)'
, 'dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0)' as 'Beginning of previous quarter (Calendar)'
, 'dateadd(yy, datediff(yy, 0, @ThisDate), 0)' as 'Beginning of this year'
, 'dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0)' as 'Beginning of next year'
, 'dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0)' as 'Beginning of previous year'
...huh. Do you have any idea how many times I've explained how using the two functions together works to someone? And how much of my life I now know is wasted, because you created this lovely thing that I could just pass along and tell people to feed data into? I hate you.
Sean, did you pull that from Lynn's blog post?
When 'splaining it, I usually just hit Lynn's blog post on the subject.
Yes it is from his blog. When 'splaining it to other I do the same. I was just posting that I have created this as a sproc in my admin db to make it easy to get for myself.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2014 at 8:13 am
jcrawf02 (4/1/2014)
Sean Lange (3/31/2014)
Ed Wagner (3/31/2014)
rodjkidd (3/31/2014)
I do like paying it forward.Got an email from friend who was stuck on working out how many days old a debt is at the end of the month, and then flagging any that are 361 days or more.
To my trusty SSC briefcase and Lynn's common date routines, to work out the last day of the month... Because I don't remember them, just need to remember Lynn wrote an article on them! :w00t:
Cheers again to the forum and people taking the time to answer questions and post articles
Rodders...
I know this article well. It has come in handy many times.
+1
In fact, I have referred to that article so many times I just created myself a stored proc to return the info to me without having to load the page. 😀
create procedure [dbo].[CommonDateRoutines] as
select 'dateadd(dd, datediff(dd, 0, @ThisDate), 0)' as 'Beginning of this day'
, 'dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0)' as 'Beginning of next day'
, 'dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0)' as 'Beginning of previous day'
, 'dateadd(wk, datediff(wk, 0, @ThisDate), 0)' as 'Beginning of this week (Monday)'
, 'dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0)' as 'Beginning of next week (Monday)'
, 'dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0)' as 'Beginning of previous week (Monday)'
, 'dateadd(mm, datediff(mm, 0, @ThisDate), 0)' as 'Beginning of this month'
, 'dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)' as 'Beginning of next month'
, 'dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0)' as 'Beginning of previous month'
, 'dateadd(qq, datediff(qq, 0, @ThisDate), 0)' as 'Beginning of this quarter (Calendar)'
, 'dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0)' as 'Beginning of next quarter (Calendar)'
, 'dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0)' as 'Beginning of previous quarter (Calendar)'
, 'dateadd(yy, datediff(yy, 0, @ThisDate), 0)' as 'Beginning of this year'
, 'dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0)' as 'Beginning of next year'
, 'dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0)' as 'Beginning of previous year'
...huh. Do you have any idea how many times I've explained how using the two functions together works to someone? And how much of my life I now know is wasted, because you created this lovely thing that I could just pass along and tell people to feed data into? I hate you.
It is directly from Lynn's blog. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
That should be in your briefcase and on your favorites. It is scary how frequently I refer people to that and how often I use it myself.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2014 at 8:42 am
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
Sir, you wound me!
I demand satisfaction!
SQL queries at 10 paces, tomorrow at dawn!
:hehe:
But, yes, I am actually 25 days away from hitting 44...
Of course, I still sometimes act like 18...
April 1, 2014 at 8:46 am
jasona.work (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
Sir, you wound me!
I demand satisfaction!
SQL queries at 10 paces, tomorrow at dawn!
:hehe:
But, yes, I am actually 25 days away from hitting 44...
Of course, I still sometimes act like 18...
K - it's dawn of tomorrow
I had you pegged for younger. Of course it is hard to tell age until you meet somebody.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2014 at 10:16 am
SQLRNNR (4/1/2014)
jasona.work (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
Sir, you wound me!
I demand satisfaction!
SQL queries at 10 paces, tomorrow at dawn!
:hehe:
But, yes, I am actually 25 days away from hitting 44...
Of course, I still sometimes act like 18...
K - it's dawn of tomorrow
I had you pegged for younger. Of course it is hard to tell age until you meet somebody.
Well, one nice thing is, most people who try to guess my age in person tend to come in 10-15 years under my actual. I think the ones who get closest are also the ones guessing high because they figure I'm trying to pull a fast one on them, and therefore have to be older than I look...
To be honest, I don't really care, as long as I can keep doing what I enjoy, I'm good.
Now, as for our little showdown, whose dawn tomorrow?
April 1, 2014 at 10:18 am
jasona.work (4/1/2014)
SQLRNNR (4/1/2014)
jasona.work (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
Sir, you wound me!
I demand satisfaction!
SQL queries at 10 paces, tomorrow at dawn!
:hehe:
But, yes, I am actually 25 days away from hitting 44...
Of course, I still sometimes act like 18...
K - it's dawn of tomorrow
I had you pegged for younger. Of course it is hard to tell age until you meet somebody.
Well, one nice thing is, most people who try to guess my age in person tend to come in 10-15 years under my actual. I think the ones who get closest are also the ones guessing high because they figure I'm trying to pull a fast one on them, and therefore have to be older than I look...
To be honest, I don't really care, as long as I can keep doing what I enjoy, I'm good.
Now, as for our little showdown, whose dawn tomorrow?
Dawn of tomorrow just passed. I guess you were sleeping.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2014 at 10:22 am
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
I shouldn't talk about ages in here. It might hurt some people. 😀
April 1, 2014 at 10:34 am
Ed Wagner (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
At 46, I don't consider myself old, but there are times when I sure do feel like it.
Agreed, I didn't think of myself as old but I sure feel like it at the moment.
April 1, 2014 at 10:37 am
Lynn Pettis (4/1/2014)
Ed Wagner (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
At 46, I don't consider myself old, but there are times when I sure do feel like it.
Agreed, I didn't think of myself as old but I sure feel like it at the moment.
And how many 12+ hour days have you worked in a row? I think I remember reading something about that recently.
April 1, 2014 at 10:48 am
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
Ed Wagner (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
At 46, I don't consider myself old, but there are times when I sure do feel like it.
Agreed, I didn't think of myself as old but I sure feel like it at the moment.
And how many 12+ hour days have you worked in a row? I think I remember reading something about that recently.
Well, there was July 2, 2013 through January 31, 2014. I have been back in Afghanistan working since February 27th.
April 1, 2014 at 10:54 am
Luis Cazares (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
I shouldn't talk about ages in here. It might hurt some people. 😀
Indeed. 😀
I shall wisely keep my age for myself 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 1, 2014 at 11:04 am
Lynn Pettis (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
Ed Wagner (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
At 46, I don't consider myself old, but there are times when I sure do feel like it.
Agreed, I didn't think of myself as old but I sure feel like it at the moment.
And how many 12+ hour days have you worked in a row? I think I remember reading something about that recently.
Well, there was July 2, 2013 through January 31, 2014. I have been back in Afghanistan working since February 27th.
From my personal experience, that might have something to do with it. I know when I work continuously without any down time, it does take a toll. It makes me feel old and tired and I notice that stupid mistakes creep their way in. I don't know what kind of stuff there is for you to do over there that's safe, but taking some time away from the computer might help. Take it with a grain of salt, though, if you're the type of person who can keep at it with no breaks at all. I just know that I really value down time.
April 1, 2014 at 11:07 am
Koen Verbeeck (4/1/2014)
Luis Cazares (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
I shouldn't talk about ages in here. It might hurt some people. 😀
Indeed. 😀
I shall wisely keep my age for myself 😀
So what is there to hurt? If you're young, middle age or old, does it really matter? I think the state of mind matters more than anything.
April 1, 2014 at 11:09 am
Ed Wagner (4/1/2014)
Koen Verbeeck (4/1/2014)
Luis Cazares (4/1/2014)
SQLRNNR (4/1/2014)
Ed Wagner (4/1/2014)
Lynn Pettis (4/1/2014)
jasona.work (4/1/2014)
...OK, old man rant off (crap, 44 is just 3.5 weeks away!)
You're not old!
Personally, I really hope you're not old at 44. 😉
Personally I hope you're not 44 cuz that is really old.
I shouldn't talk about ages in here. It might hurt some people. 😀
Indeed. 😀
I shall wisely keep my age for myself 😀
So what is there to hurt? If you're young, middle age or old, does it really matter? I think the state of mind matters more than anything.
Err, states of mind.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2014 at 11:29 am
Hey Steve (if you're listening), can I indirectly toss some flames into that WIT editorial comments?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 43,561 through 43,575 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply