Convert DateTime Time from 00:00:00 to 23:59:59

Question

In my database table fields are saved as 2013-02-15 00:00:00.000. I want they should be 2013-02-15 23:59:59.999. So how to convert 2013-02-15 00:00:00.000 to 02-15 23:59:59.999. In other words only change minimum to maximum time.

What kind of testing? I bet it's better to be done at comparasing then modifying data — Yuriy Galanter, Apr 09 '14 at 17:53
@YuriyGalanter, In have live app which include a bug that need to be fixed quickly. My db have 00:00:00.000 in time field. My business check current date with current time which is causing this issue. — Imran Qadir Baksh - Baloch, Apr 09 '14 at 17:55
What datatype are you using? In SQL 2012, it can make a big difference. — Philip Kelley, Apr 09 '14 at 17:57
Then you'll have to use .997 -- is that bad? (More detailed answer pending, though someone will probably beat me to it) — Philip Kelley, Apr 09 '14 at 17:58

Mudassir Hasan · Accepted Answer · 2014-04-09T18:08:38.140

4

DECLARE @Time TIME = '23:59:59.999'
SELECT dateColumn + @Time
FROM tableName

SQL Fiddle Demo

Edit

Cast @time to datetime before (+)

DECLARE @Time TIME = '23:59:59.999'
SELECT dateColumn + CAST(@Time as DATETIME)
FROM tableName

edited Apr 09 '14 at 18:08

answered Apr 09 '14 at 17:53

Mudassir Hasan

28,083
20
99
133

1

I think due to http://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond – Imran Qadir Baksh - Baloch Apr 09 '14 at 17:59
@user960567 thanks for pointing out the thread..didn't know about precesion – Mudassir Hasan Apr 09 '14 at 18:01
1

I am using SQL Server 2014 and getting this error. `Msg 402, Level 16, State 1, Line 2 The data types datetime and time are incompatible in the add operator.` – abhi Apr 09 '14 at 18:03
cast @time to datetime – Mudassir Hasan Apr 09 '14 at 18:12

score 2 · Answer 2 · answered Apr 09 '14 at 17:56

2

Easily done:

SELECT dateCol + '23:59:59'

answered Apr 09 '14 at 17:56

T McKeown

12,971
1
25
32

Actually this will produce the next day date. Dunno why – Yuriy Galanter Apr 09 '14 at 17:57
I think due to http://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond – Imran Qadir Baksh - Baloch Apr 09 '14 at 17:58

score 2 · Answer 3 · answered Apr 09 '14 at 18:05

The datetime datatype is not accurate to thousandths of seconds, but rather only to 333rds of a second (details in BOL, it has to do with how the data is actually stored).

Run this for an example:

DECLARE @Test datetime = 'Feb 15, 2013'

PRINT convert(varchar(50), @Test, 109)

SET @Test = dateadd(ms, -1, @Test)
PRINT convert(varchar(50), @Test, 109)

SET @Test = dateadd(ms, -1, @Test)
PRINT convert(varchar(50), @Test, 109)

SET @Test = dateadd(ms, -1, @Test)
PRINT convert(varchar(50), @Test, 109)

PRINT ''

SET @Test = dateadd(ms, -3, @Test)
PRINT convert(varchar(50), @Test, 109)

SET @Test = dateadd(ms, -3, @Test)
PRINT convert(varchar(50), @Test, 109)

SET @Test = dateadd(ms, -3, @Test)
PRINT convert(varchar(50), @Test, 109)

This generates:

Feb 15 2013 12:00:00:000AM
Feb 15 2013 12:00:00:000AM
Feb 15 2013 12:00:00:000AM
Feb 15 2013 12:00:00:000AM

Feb 14 2013 11:59:59:997PM
Feb 14 2013 11:59:59:993PM
Feb 14 2013 11:59:59:990PM

So, the simplest way to update a datetime to the highest/last possible moment prior to, say, Feb 15, 2013, you need to subtract 3 milliseconds, as shown above. In a table, it would be something like:

UPDATE MyTable
 set MyDateTime = dateadd(ms, -3, MyDateTime)

SQL 2012 introduced datatypes that provide extreme precision for time, if you need them. — Philip Kelley, Apr 09 '14 at 18:07

Convert DateTime Time from 00:00:00 to 23:59:59

3 Answers3