| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-26 : 07:58:26
|
| Mala writes "Hi,Can you please tell me is there any function which returns lastday of the given month. Thanks in advance.Bye,Mala" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-26 : 08:27:27
|
sure.declare @d datetimeset @d = getdate()select dateadd(mm,datediff(mm,0,@d),0) - 1 Jay White{0} |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-11-26 : 08:54:50
|
select dateadd(mm,datediff(mm,0,@d) + 1,0) - 1  |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-26 : 09:14:05
|
right ...  Jay White{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-26 : 13:06:09
|
| Since were on the topic of dates:I use datepart to get the begginning and end of months but using the 1900 date 0 offset should be faster.Is there another faster way to get the beggining using the same idea.Here's what I currently use.SELECT dateadd(d,-datepart(d,@d)+1,@d) |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-26 : 13:11:12
|
| Answer:SELECT dateadd(mm,datediff(mm,0,@d),0) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-11-26 : 13:27:04
|
| Now this type of thing should be on some sort of "Standard Scripts" page that we were talking abotu a few weeks ago!Great stuff in this topic guys! Very enlightening!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-11-26 : 15:19:57
|
The way I see it, it just takes less room in my head if I can use the same approach for start-of-year, -quarter, -month, -week, -day and -hour  Sadly, it doesn't work particularly well for minutes and seconds because there aren't enough numbers.Edited by - Arnold Fribble on 11/26/2002 15:22:54 |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-26 : 15:59:35
|
| If you know the starting date of all data you'll be dealing withcould you change the offset from zero to something that will bring the number of minutes diff into valid range(2,147,483,647 gives approximately 68 years worth of seconds to play with). Well it's something to try.Edited by - ValterBorges on 11/26/2002 16:17:03 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-26 : 16:09:02
|
quote:
Sadly, it doesn't work particularly well for minutes and seconds because there aren't enough numbers.
Well, if you really ARE using date values after January 24, 5983, then no, it won't work for minutes.  And if you had to round off something to the nearest second:SELECT DateAdd(ms, -DatePart(ms, getdate()), getdate()) |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-11-26 : 16:15:06
|
Huh! Not inviting you to my Y10K party.  |
|
|
|
|
Date Function