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]

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]

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:v4********************************@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)

[Erland Sommarskog]

RDRaider (rd******@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, es****@sommarskog.se

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

[RDRaider]

Thanks, that did the trick.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

RDRaider (rd******@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, es****@sommarskog.se

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