469,918 Members | 2,201 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,918 developers. It's quick & easy.

how to update a datetime field

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
Jul 23 '05 #1
4 42839
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)
Jul 23 '05 #2
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)

Jul 23 '05 #3
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
Jul 23 '05 #4
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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Phil Powell | last post: by
reply views Thread by M. Bader | last post: by
6 posts views Thread by FatboyCanteen | last post: by
1 post views Thread by Thierry | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.