Reset date to first of month

I receive an existing date variable, which I copy to my @.today variable. I
need to reset the day of the month to be the first of the month, leaving all
other aspects of @.today alone.
Any suggestions?
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--the following doesn't work, but it shows what I want to do
SET MONTH(@.today) = 1
SELECT @.today
Thanks
--
RandySET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
Steve Kass
Drew University
randy1200 wrote:

>I receive an existing date variable, which I copy to my @.today variable. I
>need to reset the day of the month to be the first of the month, leaving al
l
>other aspects of @.today alone.
>Any suggestions?
>-- simulate the date I get, which I have no control over
>declare @.date datetime
>set @.date = '02/08/2008'
>--assign to my date variable, which I do control
>declare @.today datetime
>SET @.today = @.date
>--the following doesn't work, but it shows what I want to do
>SET MONTH(@.today) = 1
>SELECT @.today
>Thanks
>|||You can try using:
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--If I understood correctly
set @.today = @.today - (DAY(@.today)-1)
SELECT @.today
Let me know if it helps..
"randy1200" wrote:

> I receive an existing date variable, which I copy to my @.today variable. I
> need to reset the day of the month to be the first of the month, leaving a
ll
> other aspects of @.today alone.
> Any suggestions?
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
> --
> Randy|||That did it. Many thanks.
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can try using:
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --If I understood correctly
> set @.today = @.today - (DAY(@.today)-1)
> SELECT @.today
> Let me know if it helps..
> "randy1200" wrote:
>|||That did it. Many thanks.
--
Randy
"Steve Kass" wrote:

> SET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
> Steve Kass
> Drew University
> randy1200 wrote:
>
>|||This one will work for the first date as well:
set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
end)
"randy1200" wrote:
> That did it. Many thanks.
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Very . Many thanks again!
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> This one will work for the first date as well:
> set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
> end)
> "randy1200" wrote:
>|||You are very welcome!
"randy1200" wrote:
> Very . Many thanks again!
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Hi Randy,
Try:
select DATEADD(mm, DATEDIFF(mm,0,@.today), 0)
"DATEDIFF(mm,0,getdate())" calculates the number of months between the curre
nt
date and the date "1900-01-01 00:00:00.000".
Remember date and time variables are stored as the number of milliseconds
since "1900-01-01 00:00:00.000"; this is why you can specify the first datet
ime
expression of the DATEDIFF function as "0."
Now the last function call, DATEADD, adds the number of months between the
current date and '1900-01-01".
By adding the number of months between our pre-determined date '1900-01-01'
and the current date, you are able to arrive at the first day of the current
month.
In addition, the time portion of the calculated date will be "00:00:00.000."

> I receive an existing date variable, which I copy to my @.today
> variable. I
> need to reset the day of the month to be the first of the month,
> leaving all
> other aspects of @.today alone.
> Any suggestions?
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
>sql