datetime image

How to Update Only Date or Time Portion of DateTime Value in SQL

Updating/Modifying date or time portion separately in a DateTime value can be a toothache in SQL. The following is a simple procedure on how to do it.

In SQL Server use the DATEADD functions to easily perform calculations such as adding or subtracting days or Hours in a DateTime value.

For example if you have an OrderDate ‘15/10/2016 8:00:00 AM’ and you want to calculate a DeliveryDate which is in 5 Months

SELECT OrderDate,DATEADD(mm,5,OrderDate)As DeliveryDate

FROM [tblPurchases]

WHERE OrderID = 1000;

Results:

DateTime Value

Syntax=DATEADD = (DatePart, Number, DateTime)

DATETIME VALUE

NB: 

DatePart in the DATEADD function determines which portion of the DATETIME values is modified

For example if you change the delivery time to 5 days

SELECT OrderDate,DATEADD(dd,5,OrderDate)As DeliveryDate

FROM [tblPurchases]

WHERE OrderID = 1000;

Results:

DateTime Value 5

 

You can also modify the Time Portion only by changing the delivery time to 5 hours.

SELECT OrderDate,DATEADD(hh,5,OrderDate)As DeliveryDate

FROM [tblPurchases]

WHERE OrderID = 1000;

Results:

DateTime Value 4

Try using the different and observe which part of the DateTime value gets updated.

In the next article we see how to perform same Operations in Excel.

 

Leave a Comment:

Your email address will not be published. Required fields are marked *