By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

how to update a datetime field

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.