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

On Tue, 11 Jan 2005 20:56:17 GMT, RDRaider wrote:

>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

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)

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_rFact.in_SPAM_fo> wrote in message
news:v4h8u0hqua5athebnjnfsfc873kdp7vec3@4ax.com...
> On Tue, 11 Jan 2005 20:56:17 GMT, RDRaider wrote:
>
>>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
>
> 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 (rdraider@sbcglobal.net) writes:
> 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.

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



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks, that did the trick.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns95DC1F854793Yazorman@127.0.0.1...
> RDRaider (rdraider@sbcglobal.net) writes:
>> 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.
>
> UPDATE tbl
> SET field = dateadd(DAY, -5, field)
> FROM tbl
> WHERE field >= '19950312' AND field < '19950313'
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp