Need help to intrepret SQL code

[samiam07]

Using SQL Server 2008 R2
Trying to figure out out what date this statement produces with a starting date of mydate=10/1/2010

CONVERT(datetime, CONVERT(char(11), DATEADD(dd, - (DAY(DATEADD(mm, 1,	   drh1.mydate)) - 1), drh1.mydate), 101))

I've tried to break it down piece by piece and pulling the hair I have left.
Thanks for any help

 

[gmmastros]

The easiest way to SEE the results would be to open a new query window and test it, like this:

Declare @Test DateTime
Set @Test = '10/1/2010'

select CONVERT(datetime, CONVERT(char(11), DATEADD(dd, - (DAY(DATEADD(mm, 1,       @Test)) - 1), @Test), 101))

It's a lot easier to see what it does than it is to understand what it does.

To help understand what it does, you should start with the inner most part of the code and work your way out, like this:

Declare @Test DateTime
Set @Test = '10/1/2010'

Select DATEADD(mm, 1, @Test)
Select DAY(DATEADD(mm, 1, @Test))
Select - (DAY(DATEADD(mm, 1, @Test)) - 1)
Select DATEADD(dd, - (DAY(DATEADD(mm, 1, @Test)) - 1), @Test)
Select CONVERT(char(11), DATEADD(dd, - (DAY(DATEADD(mm, 1, @Test)) - 1), @Test), 101)
select CONVERT(datetime, CONVERT(char(11), DATEADD(dd, - (DAY(DATEADD(mm, 1, @Test)) - 1), @Test), 101))

Now... it's a shame that you mention 10/1/2010 in your original question because it appears as though this code doesn't do anything. It returns 10/1/2010 when you enter 10/1/2010.

If you change your test data to 1/15/2010 10:30AM, you can see what it is actually doing.

Declare @Test DateTime
Set @Test = '10/15/2010 10:31AM'

Select DATEADD(mm, 1, @Test)
Select DAY(DATEADD(mm, 1, @Test))
Select - (DAY(DATEADD(mm, 1, @Test)) - 1)
Select DATEADD(dd, - (DAY(DATEADD(mm, 1, @Test)) - 1), @Test)
Select CONVERT(char(11), DATEADD(dd, - (DAY(DATEADD(mm, 1, @Test)) - 1), @Test), 101)
select CONVERT(datetime, CONVERT(char(11), DATEADD(dd, - (DAY(DATEADD(mm, 1, @Test)) - 1), @Test), 101))

This is NOT the most efficient code for returning the first day of the month, but that is what it does.

A simpler and more efficient approach is:

Declare @Test DateTime
Set @Test = '10/15/2010 10:31AM'

Select DateAdd(Month, DateDiff(Month, 0, @Test), 0)

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[clapag22]

Looks like it returns the first day of the month for the date you pass in. Change mydate to 10/17/2010 and run. You will get 10/1/2010.

 

[samiam07]

Thank you, Thank you
I came up with the same thing and couldn't believe it.
Why was it written this way just to get the first of the month when all the dates in this field are by definition and default the first of some bloody month??????

Oh well! Whenever someone leaves a contract and some other contractor picks up the maintenence there will always be questions like this.

Thank you both again. I will/shall/going to use the tips you listed.

 

[JoeAtWork]

Why was it written this way just to get the first of the month when all the dates in this field are by definition and default the first of some bloody month??????

Are you sure that what is in the database will always be the first of the month? Are the users able to enter any date, regardless of what they are supposed to enter? It looks like the originally programmer anticipated that sometimes the first of the month would not be entered, so there is probably a reason since most of us don't do extra work for nothing.