Any good with dates?? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Any good with dates??

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-03-10 : 07:30:55

Hi,

I have a sproc that recieves a date called @DateAssessed from that date I want to create two more

variables called @FromDate and @ToDate.

@FromDate has to be the beginning of the month before, one year ago using @DateAssessed

@ToDate has to be the end of the month before using @DateAssessed

So if @DateAssessed = 11/05/03 then @DateFrom would be 01/04/02 and @DateTo would be 30/04/03

How would I assign the dates to the variables?

Thanks

Leah

Edited by - leahsmart on 03/10/2003 08:45:34

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2003-03-10 : 07:52:09

Select cast(Month(dateadd(m, -1, '05/11/03')) as varchar(2)) + '/01/'+ cast(Year(dateadd(yy, -1, '05/11/03')) as varchar(4)) FromDate

I am not sure I understand exactly how you are coming up with the
to date, but I use the following to get the last day of the month.

dateadd(d, -1,
cast(cast(Month(dateadd(m, 1, '05/11/03')) as varchar(2)) + '/01/'+ cast(Year('05/11/03') as varchar(4))) as datetime)

Brief explanation. I add one month to the date, concatenate the day of '/01/' to it and then concatenate the year ('03') to it. Now you have the first day of the next month. Then you simply need to subtract one day from that date.

Hope this helps.

Jeremy

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-10 : 08:17:31

Also, if you like take a look at my function at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

Itmakes manipulating dates a little more easy without lots of converts. Works like DateSerial() in VB.

Then, @FromDate is:

dbo.MDate(Year(@DateAssessed)-1,Month(@DateAssessed)-1,1)

And @ToDate is

dbo.MDate(Year(@DateAssessed),Month(@DateAssessed),1)-1

Note that my function takes arguments in the order of year, month, day so mm/dd or dd/mm format should not make a difference.

Finally, question: What is the @FromDate if the DateAssessed is january 2000? Is it december 1, 1998 ? If so, the above should work fine for you. If not, you will need to handle that case as well.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-10 : 08:29:35

I too am confused by @DateTo - end of month before is 20030430

You can do it using date functions

select @DateFrom = dateadd(mm,-1,dateadd(yy,-1,dateadd(dd,(datepart(dd,@DateAssessed)*-1)+1,@DateAssessed)))
select @DateTo = dateadd(dd,(datepart(dd,@DateAssessed)*-1),@DateAssessed)

gives 20020401 and 20030430

If you want @DateTo to be the end of 2 months ago - i.e. 1 year between @DateFrom and @DateTo

select @DateFrom = dateadd(mm,-1,dateadd(yy,-1,dateadd(dd,(datepart(dd,@DateAssessed)*-1)+1,@DateAssessed)))
select @DateTo = dateadd(dd,-1,dateadd(mm,-1,dateadd(dd,(datepart(dd,@DateAssessed)*-1)+1,@DateAssessed)))

gives 20020401 and 20030331

if you want last day of two months ago then just change the month subtract to 2.

select @DateFrom = dateadd(mm,-1,dateadd(yy,-1,dateadd(dd,(datepart(dd,@DateAssessed)*-1)+1,@DateAssessed)))
select @DateTo = dateadd(dd,-1,dateadd(mm,-2,dateadd(dd,(datepart(dd,@DateAssessed)*-1)+1,@DateAssessed)))

gives 20020401 and 20030228

Does anyone elses browser think dd,@DateAssessed is an email address?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/10/2003 08:31:49

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-03-10 : 08:46:44

Yes I would be confused as well. Sorry I put in the wrong date for @ToDate, I have changed the origional post.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-10 : 11:12:12

quote:

Does anyone elses browser think dd,@DateAssessed is an email address?

Mine does!!

But i dont think it has anything to do with the browser, the forum software adds the "mailto" tags...

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources