473,854 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 43155
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_rFa ct.in_SPAM_fo> wrote in message
news:v4******** *************** *********@4ax.c om...
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******@sbcgl obal.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****@sommarsk og.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****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1...
RDRaider (rd******@sbcgl obal.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****@sommarsk og.se

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

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
9468
by: Phil Powell | last post by:
I have a mySQL database table with a column field datatype of datetime that I set up in the original create table statement like this: create table nnet_usermetadata ( .... nnet_record_entered datetime default 'now()' ) However, upon execution, each time a record is entered into nnet_usermetadata the results are:
0
1492
by: M. Bader | last post by:
Hi, i'm struggling with updating session expire times in my login table. Can you tell me, how to correctly add an amount of seconds (after that the session expires) to a datetime field? when i do the insert for a new login, or an update, the expire field will alway end up containing zeros
0
5361
by: Jim S. | last post by:
I'm having a horrible time simply inserting the date in a MySQL database through a Visual Basic program. I have a table, called "ORDERS"; "ID" is the primary key. I'm trying the insert the date, into the MySQL database, which is a DATETIME datatype. I must keep it DATETIME so it can be accessed via Microsoft Access. Here are two versions of the program. The first one fails, with the "Row cannot be located for updating. Some values may...
6
16268
by: FatboyCanteen | last post by:
When I using dataset to append a null value to the datetime field. It throw a error -> can not convert db.null to system.date Can there is any standard to pass a Null value to the DateTime Field! (It is successful to pass the Null Value to other type of Field)
1
2003
by: Thierry | last post by:
Hi, I've got the next problem: I have a datetime field in my database (sql-server) and I want the option in it that this field can be empty. But if I leave that datetime field empty I get the error: "The value you entered is not consistent with the data type or length of the column."
1
4801
by: RML | last post by:
Hi everyone, I am using VB.NET 2003 and an OleDBDataAdapter to update an Access table's DateTime field. The field's format is set to "General Date" (ie: 11/24/2004 8:00:00 AM). The problem is the field ends up with only a date, with no time component. In my project... I added an OleDBDataAdapter using the wizzard to my form. I generated the DataSet and added the desired table. I verified the field type in the
4
4609
by: kd | last post by:
Hi All, I have a datetime column in a table on the SQL database. I need to insert values into the datetime column from vb.net code. Here is my code: dim nameval, str, qry as string nameval = "abc" str = "2005/03/16 14:20" qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'" str & "')" ....
7
6806
by: bjose21 | last post by:
Hello, I've been searching the web for quite some time to resolve the problem of "1/1/1900" returning in a datetime field in SQL that resulted from a blank (not NULL) value being passed to it through an ASP page. The solution is that a NULL value needs to passed to SQL from ASP. Thats fine...I understand the why the problem is happening and the solution around it. HOWEVER, I can't seem to get the proper syntax to work in the ASP...
3
5326
by: john | last post by:
I am using MS Sql Express as the backend database for a Microsoft Access front end application. I am using a calendar control to store the desired date in the sql datetime field. The time portion of the field defaults to 12:00am. What I want is to also store the time in the same datetime field as the date, but I want to use a separate control from the calendar control. I.e. I want the user to choose the date from the calendar control,...
0
9903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11041
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10373
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9525
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7921
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5753
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4565
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3193
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.