Finding Current Month's First Day& Last Day

Question

Finding Current Month's First Day& Last Day

Viewing 15 posts - 31 through 45 (of 45 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Sanjay Pandey (1/2/2008)
Sanjay Pandey (12/21/2007)
Using Datefunctions.
select getdate()-day(getdate()) as LastDayLastMth, getdate()-(day(getdate())-1) as FirstDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate())) as LastDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate()))+1 as FirstDayNextMth
Friends, please analyse the above one also in comparision to datediff. I use it always and would be interested in knowing, whether this is ok or I should switch to datediff.
To me it looked like using plain subtraction, instead of calling another function is faster.
Comments please.

Ummm... you should actually stop using those "functions" immediately because they do NOT strip off the time element of GETDATE(). In fact, you should really make plans to retrofit all the procs you've used them in because of that.

So far as simple subtraction goes, let's examine your "FirstDayNextMth" function...

SELECT dateadd(m,1,getdate())-day(dateadd(m,1,getdate()))+1 as FirstDayNextMth

Not including GETDATE(), you have 3 DATETIME functions; 2 DATEADD's and 1 DAY. You also have 2 separate math operations... 1 Subraction and 1 Addition. Throw in the 2 GETDATE's, and you have a total of 5 DATETIME functions and 2 basic math operations.

Now, let's consider the method that a lot of us use instead...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0) AS FirstDayNextMonth

Including the GETDATE(), there are only 3 datetime functions and 1 math operator. It also has the benefit of doing it correctly because it sets the time element to midnight of the returned day.

Hope that helps...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jeff Moden (1/2/2008)
Heh... for the same reasons, I do the very same thing. In fact, I incorporated that same method into the SQL Standards I wrote for work because so many of the developers thought that subtracting 1ms would do the trick...

Why do you think I write the date functions now? The standards require that the date functions are used. Whether the developer prefers to do between with the start and end, or >=, &lt on just the start dates, I don't mind. As long as they don't try to write their own date functions.

The last date function that I saw one of the devs write (DateLastMonthEnded) only worked if the previous month has as many or fewer days than the current month.

I do believe, though, that the resolution actually pans out to 3.3 milliseconds, but I could be wrong and it certainly won't matter using th whole date method. 😉

Yup. It rounds to the closest whole number though. The milliseconds round to 0, 3 or 7

So, how come I've interviewed (count 'em!) 5 folks who supposedly have Masters degrees in Mathematics and one with a PHD that couldn't tell me the decimal equivalents for 14₁₆, 2⁰, or even 2³????

Are you serious? I could see the 14₁₆ catching someone for a second, but the other 2.... That's not even 1st year maths stuff.

Master's in CS couldn't tell me what would happen to bit 3 of a byte if I multiplied the byte by 2.

*blink* Even if it's not obvious what will happen, they could convert 100 into dec, multiply by 2 and convert back. *shudder*

I believe that if you claim to be a "Senior Java Developer" with "5 to 8 years experience", that you should be able to tell me how to keep a web user from saving the same record more than once by hitting the SAVE button more than once during the same session.

I'm not a java dev, but.. disable the button in the onclick event? that's probably what I'd do in C#

(I'm now probably going to get 40 posts telling me I'm a crap C# developer. Yes, I know that aleady)

And I thought I got clueless people appying here

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 3

Jeff: Gis a Job then mate!:)

ROFL! I would shoot these people before they could damage the ecconomy! I might not be able to spell but maths like that I was doing aged 12.:)

anything to the power zero is 1 🙂

multplying by 2 in binary shifts all the bits in byte (or any binary value) to the left, appending 0 to the end. 🙂

14 in base 16 is 14 in base 10! Doh!

Can I claim to have a masters degree in maths?:D

Hmm not so sure about the java question but I would disable the button once it was clicked and it would remain disabled until an onchange event occured in one of the input boxes of the web page and the actual contents of said input box changed - i.e. if the user deletes a character and the retypes the same character the button is enabled for after the deletion and then disabled again upon keyup event for the retyped charcter.

Can I have three PHDs from any US University please?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 4

Damn! I should proof read what I type

14 base 16 is 20 in base 10

Doh!

But hey 2 out of 3 = 66% must be worth a 2/2 Degree 😀

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Terri-92562 SSCommitted Points: 1614 More actions · Answer 5

Shaun McGuile (1/3/2008)
Jeff: Gis a Job then mate!:)
ROFL! I would shoot these people before they could damage the ecconomy! I might not be able to spell but maths like that I was doing aged 12.:)
anything to the power zero is 1 🙂
Can I have three PHDs from any US University please?
--Shaun

Actually, zero to the power zero is undefined. I know, picky, picky - I'm a former math teacher.:)

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 6

0 to the power 0 is undefined - maybe in pure maths

But my good ol calculator says it is 1. 😀

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Terri-92562 SSCommitted Points: 1614 More actions · Answer 7

Shaun McGuile (1/3/2008)
0 to the power 0 is undefined - maybe in pure maths
But my good ol calculator says it is 1. 😀
--Shaun

And my calculator says it is an error:exclamation:

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 8

Hmm...do you have the mathematical proof that 0 raised to 0 is undefined?

What make is your calculator?

Whoops! We've hijacked a thread!:D

However my reasons for 0^0 = 1

http://mathforum.org/dr.math/faq/faq.0.to.0.power.html

Hey Terri we're both right!:D

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

Phil Factor SSC-Insane Points: 20244 More actions · Answer 9

We've hijacked a thread!

Yes, my head is swimming. I thought this thread was all about being asked about date calculations during an interview. It is lucky we don't know what else they asked otherwise we'd be here all day.

Best wishes,
Phil Factor

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Sanjay Pandey (1/2/2008)
Sanjay Pandey (12/21/2007)
Using Datefunctions.
select getdate()-day(getdate()) as LastDayLastMth, getdate()-(day(getdate())-1) as FirstDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate())) as LastDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate()))+1 as FirstDayNextMth
Friends, please analyse the above one also in comparision to datediff. I use it always and would be interested in knowing, whether this is ok or I should switch to datediff.
To me it looked like using plain subtraction, instead of calling another function is faster.
Comments please.

The biggest problem with your code is that it does not remove the time protion of the datetime.

For example, look at the output from your method and my method for the last day of last month:

select

a.Date-day(a.Date) as LastDayLastMth,

dateadd(month,datediff(month,-1,a.Date)-1,-1) as LastDayLastMth_1

from

(

select Date = getdate()union all

select Date = '20080229 23:59:59.997'

) a

Results:

LastDayLastMth LastDayLastMth_1

----------------------- -----------------------

2007-12-31 11:36:49.387 2007-12-31 00:00:00.000

2008-01-31 23:59:59.997 2008-01-31 00:00:00.000

(2 row(s) affected)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Shaun McGuile (1/3/2008)
I would shoot these people before they could damage the ecconomy!

Heh... way too late for that... especially in Michigan...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

Terri (1/3/2008)
Shaun McGuile (1/3/2008)
0 to the power 0 is undefined - maybe in pure maths
But my good ol calculator says it is 1. 😀
--Shaun
And my calculator says it is an error:exclamation:

SQL Server says one:

select Zero_to_Zero_Power = power(0,0)

Results:

Zero_to_Zero_Power

------------------

1

(1 row(s) affected)

http://www.mathforum.org/dr.math/faq/faq.0.to.0.power.html

"According to some Calculus textbooks, 0^0 is an "indeterminate form." What mathematicians mean by "indeterminate form" is that in some cases we think about it as having one value, and in other cases we think about it as having another."

dbajunior SSCrazy Points: 2307 More actions · Answer 13

All ,

I know this is not totally relevant to the discussion presented herein, but I didn't know where else to post it. (I know, Jeff, "go to the link on posting etiquette!")

This (datetime) thread is a good example of why I'm a SQL ServerCentral subscriber.

Nuggets like this one are the reason I alot up to 30 minutes first thing each morning (when i can) to perusal of my daily SSC newsletter. Very useful stuff - you should see all the scripts I've got from this site and saved in a folder!

So... many, many thanks to all...specially Steve, Jeff, GilaMonster, and numerous others that make this site one of the best resources for SQL folks. One more thing to be thankful for this year! 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

That's one of the best compliments anyone could ask for, James. Thank you, Sir!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

It's a pleasure, and thank you for the feedback.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass