SQL Server - Convert minutes to time and also todays date

Question

I have seen many examples on the internet regarding similar issues, but nothing quite what I am trying to achieve.

My Scenario

I have a table which stores an EndTime in minutes (1050) meaning 17:30:00

I have written a query to update a column in a referenced table when its null, however the date itself is set to 1900-01-01 17:30:00.000. And no matter what I try, I only get the date correct or time correct... heres the query that converts the int to time and displays the incorrect date above:

UPDATE t
SET t.EndTime = (
                SELECT CONVERT (datetime, DATEADD(MINUTE, EndTime, '00:00:00'), 108)
                FROM Staff 
                WHERE 
                    StaffID = t.StaffID 
                )
FROM TimesheetLine t
WHERE t.EndTime IS NULL

I am trying to achieve 2015-01-07 17:30:00.000

Staff.EndTime is where the 1050 minutes are stored

TimesheetLine.EndTime is the field I am trying to set to 2015-01-07 17:30:00.000 when it is null

thankyou for your help and apologies if there is a duplicate question...

So when EndTime is null, the date part should be today's date? — shree.pat18, Jul 01 '15 at 08:28
when the EndTime is null in the Timesheet table, the date part needs to be set to today (2015-01-07) and the time part needs to be (17:30:00.000) which is referenced from Staff.EndTime column — Crezzer7, Jul 01 '15 at 08:29
Your question essentially boils down to getting only the date part of a datetime value, and that has been answered previously here: http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype — shree.pat18, Jul 01 '15 at 08:36
i know it is possible to just get the date and just get the time, but writing them together in one UPDATE query is what I was aiming for to make it more efficient... I will have a look at the link above and try it out — Crezzer7, Jul 01 '15 at 08:38
Well, just replace the '00:00:00' with the formula to get today's date only. The '00:00:00' causes the date part to be defaulted to 1 Jan 1900. — shree.pat18, Jul 01 '15 at 08:41
that returns 2015-07-02 03:12:25.540 when replacing the '00:00:00' — Crezzer7, Jul 01 '15 at 08:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/82060/discussion-between-shree-pat18-and-crezzer7). — shree.pat18, Jul 01 '15 at 08:43

score 2 · Accepted Answer · answered Jul 01 '15 at 08:43

If you just want to take a date and add 17:30:00 on to it you can do something like this:

SELECT DATEADD(MINUTE, 1050, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))

So it takes a given date, in this instance I've used GETDATE() to get the current date. I've converted it to a date and then back to a datetime to strip off the time portion. Then used DATEADD() to add on the specified number of minutes.

Output:

2015-07-01 17:30:00.000

So if I read your query right you would do this:

UPDATE t
SET t.EndTime = (
              SELECT DATEADD(MINUTE, EndTime, CONVERT(DATETIME, CONVERT(DATE, GETDATE())))
              FROM Staff 
              WHERE StaffID = t.StaffID 
                )
FROM TimesheetLine t
WHERE t.EndTime IS NULL

SQL Server - Convert minutes to time and also todays date

1 Answers1