Create DateTimeOffset 00:01 / 23:59 from given DateTimeOffset for same day

Question

Warning - TSQL newbie question ahead:

I've got a DateTimeOffset for, say, 2013-03-11 11:04:26.0000000 +00:00 and I need to create two new dates for the same day, one at 00:01 and another one at 23:59.

So far I've tried various things with DATETIMEOFFSETFROMPARTS and DATEPART but it seems way to awkward - is there a simple solution to snip off the hours/minutes/etc. and just use the day replacing the time with the two required ones?

Any hints appreciated.

Take a look at Best approach to remove time part of datetime in SQL Server , maybe it's useful. — Maryam Arshi
– Maryam Arshi, Commented Mar 11, 2013 at 11:46
Rather than computing such odd dates - if they're for comparison purposes, it would be better to change the comparison operation (i.e. people frequently try to get the "last moment" of a particular day, ignoring that things can happen during the last minute (or second) of the day, and so 23:59 won't include such events). Using < the next day, rather than <= 23:59 is a better solution. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 11, 2013 at 11:46
Unfortunately we're supposed to create those date times to represent a time window for some other part of the application - no comparison involved at all.. — Jay
– Jay, Commented Mar 11, 2013 at 11:47

Oleksandr Fedorenko · Accepted Answer · 2013-03-11 14:05:23Z

1

Also you can try this option

DECLARE @date datetimeoffset = '2013-03-11 11:04:26.0000000 +00:00'
SELECT DATEADD(day, DATEDIFF(day, 0 , @date), '00:01'), 
       DATEADD(day, DATEDIFF(day, 0 , @date), '23:59') 

--Results:2013-03-11 00:01:00.000   2013-03-11 23:59:00.000

edited Mar 11, 2013 at 14:05

answered Mar 11, 2013 at 13:37

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jay · Accepted Answer · 2013-03-11 11:56:05Z

1

Got it...

select @pkgDate = CONVERT (date, @pkgDate)

select DATEADD (minute, 1, @pkgDate)
select DATEADD(hour, 23, DATEADD (minute, 59, @pkgDate))

..better/more concise ways of doing it still appreciated.

answered Mar 11, 2013 at 11:56

Jay

6,7183 gold badges40 silver badges66 bronze badges

Collectives™ on Stack Overflow

Create DateTimeOffset 00:01 / 23:59 from given DateTimeOffset for same day

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related