how to update a datetime field

[RDRaider]

How can I update only the date portion of a datetime field?
Example:
datetime field = '3/12/1995 12:05:50 PM'

How can I change just the day '12' to a '7'

Thanks

[Hugo Kornelis]

Re: how to update a datetime field

On Tue, 11 Jan 2005 20:56:17 GMT, RDRaider wrote:
[color=blue]
>How can I update only the date portion of a datetime field?
>Example:
>datetime field = '3/12/1995 12:05:50 PM'
>
>How can I change just the day '12' to a '7'
>
>Thanks[/color]

Hi RDRaider,

You're using an ambiguous date format. Is it december 3rd or March 12th?

DECLARE @dt datetime
SET @dt = '1995-12-03T12:05:50'
SELECT DATEADD(month, 7 - month(@dt), @dt)

-- Or did you mean this?
SET @dt = '1995-03-12T12:05:50'
SELECT DATEADD(day, 7 - day(@dt), @dt)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[RDRaider]

Re: how to update a datetime field

Thanks for the reply. What I really need is to change a March 12, 1995 to
March 7, 1995. But I don't want to change the time. So I have over 1500
records with the same 3/12/1995 but with different times. I need to change
the date to 3/7/1995 but retain the time field which is different for each
record.

RD

"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:v4h8u0hqua 5athebnjnfsfc87 3kdp7vec3@4ax.c om...[color=blue]
> On Tue, 11 Jan 2005 20:56:17 GMT, RDRaider wrote:
>[color=green]
>>How can I update only the date portion of a datetime field?
>>Example:
>>datetime field = '3/12/1995 12:05:50 PM'
>>
>>How can I change just the day '12' to a '7'
>>
>>Thanks[/color]
>
> Hi RDRaider,
>
> You're using an ambiguous date format. Is it december 3rd or March 12th?
>
> DECLARE @dt datetime
> SET @dt = '1995-12-03T12:05:50'
> SELECT DATEADD(month, 7 - month(@dt), @dt)
>
> -- Or did you mean this?
> SET @dt = '1995-03-12T12:05:50'
> SELECT DATEADD(day, 7 - day(@dt), @dt)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)[/color]

[Erland Sommarskog]

Re: how to update a datetime field

RDRaider (rdraider@sbcgl obal.net) writes:[color=blue]
> Thanks for the reply. What I really need is to change a March 12, 1995
> to March 7, 1995. But I don't want to change the time. So I have over
> 1500 records with the same 3/12/1995 but with different times. I need
> to change the date to 3/7/1995 but retain the time field which is
> different for each record.[/color]

UPDATE tbl
SET field = dateadd(DAY, -5, field)
FROM tbl
WHERE field >= '19950312' AND field < '19950313'



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[RDRaider]

Re: how to update a datetime field

Thanks, that did the trick.

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns95DC1F8 54793Yazorman@1 27.0.0.1...[color=blue]
> RDRaider (rdraider@sbcgl obal.net) writes:[color=green]
>> Thanks for the reply. What I really need is to change a March 12, 1995
>> to March 7, 1995. But I don't want to change the time. So I have over
>> 1500 records with the same 3/12/1995 but with different times. I need
>> to change the date to 3/7/1995 but retain the time field which is
>> different for each record.[/color]
>
> UPDATE tbl
> SET field = dateadd(DAY, -5, field)
> FROM tbl
> WHERE field >= '19950312' AND field < '19950313'
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]