Solved

SQL server add hours and minutes to datetime

Posted on 2014-03-11
3
769 Views
Last Modified: 2014-03-13
Hello: I have many records in a SQL Server 2008 database with a datecompleted set to a range of values in the year and day, but the time is 00:00:00.

I need to update these records individually and set the time to 23:58:00 while leaving the year/day as it is.

What's the safe and proper syntax to do that?

Thanks
0
Comment
Question by:KnowledgeWare
3 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 250 total points
ID: 39922253
Loads of ways, one is:

update MyTable set DateCompleted = dateadd(mi, 58, dateadd(hi, 23, DateCompleted))
0
 
LVL 7

Assisted Solution

by:Kishan Zunjare
Kishan Zunjare earned 250 total points
ID: 39925574
I believe you need the DATEADD function:

DECLARE @Date DAtetime
SET @Date = DATEADD(hh, 23, '03/13/2014')
SET @Date = DATEADD(mi, 58, @Date)
SELECT @Date
0
 

Author Closing Comment

by:KnowledgeWare
ID: 39926943
Thank you!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question