6

How can I set only the time portion of a SQL datetimeoffset variable?

For example:

DECLARE @date datetimeoffset = '2014-01-11 11:04:26 +02:00'

I want to calculate two further datetimeoffsets:

@startdate should be '2014-01-11 00:00:00 +02:00'
@enddate should be '2014-01-12 00:00:00 +02:00'

But all the solutions I tried set the offset to zero.

NOTE: I need the original offset in the results since I need to compare them to some datetimeoffset columns in a table. So just casting to a date will not work.

Further note: This would be quite easy in SQL2012; I could use DATETIMEOFFSETFROMPARTS. Unfortunately, I am not able to upgrade to 2012 at the moment. Example:

SET @startdate = SELECT DATETIMEOFFSETFROMPARTS(datepart(yyyy, @date), datepart(mm, @date), datepart(dd, @date), 0, 0, 0, 0, datepart(tz, @date), 0, 7);
Kev
  • 2,656
  • 3
  • 39
  • 63

2 Answers2

6
DECLARE @date DATETIMEOFFSET = '2014-01-11 11:04:26 +02:00'

SELECT TODATETIMEOFFSET(CAST(@date AS date),DATEPART(tz,@date))
SELECT TODATETIMEOFFSET(DATEADD(day,1,CAST(@date AS date)),DATEPART(tz,@date))
Anon
  • 10,660
  • 1
  • 29
  • 31
3
DECLARE @date DATETIMEOFFSET = '2014-01-11 11:04:26 +02:00'

SELECT @date InputDate,
CONVERT(DATETIMEOFFSET,(CONVERT(VARCHAR(20),CONVERT(DATETIME,(CONVERT(DATETIME,CONVERT(DATE,@date)))) ) + ' ' + RIGHT(@date,6))) StartDate,
CONVERT(DATETIMEOFFSET,(CONVERT(VARCHAR(20),CONVERT(DATETIME,(CONVERT(DATETIME,CONVERT(DATE,DATEADD(day,1,@date))))) ) + ' ' + RIGHT(@date,6))) EndDate
Devart
  • 119,203
  • 23
  • 166
  • 186
Kishore
  • 846
  • 6
  • 9
  • Thank you this works great. Do you mind explaining it a bit more? It seems to piece together a string from the parts of the input date. – Kev Jan 13 '14 at 13:33
  • Ok. CONVERT(VARCHAR(20),(CONVERT(DATETIME,CONVERT(DATE,@date))) the above part is used to get the date from the input and convert it into datetime so explicit conversion here makes the time to be 00:00.00 – Kishore Jan 13 '14 at 13:36
  • 2. here am getting offset value from input using string functions (Right function) because datefunction have some restrictions over Timeoffset – Kishore Jan 13 '14 at 13:37