1

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.

Jay
  • 6,572
  • 3
  • 37
  • 65
  • Take a look at [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) , maybe it's useful. – Maryam Arshi Mar 11 '13 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 Mar 11 '13 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 Mar 11 '13 at 11:47

2 Answers2

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.

Jay
  • 6,572
  • 3
  • 37
  • 65
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
Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44