CAST datetimeoffset(7) as a datetime in UK format

Question

CAST datetimeoffset(7) as a datetime in UK format

tylerschuler75

SSC Enthusiast

Points: 117
More actions
December 8, 2025 at 8:09 pm

#4697349

I have a view where I am casting a datetimeoffset(7) field to smalldatetime or datetime as my reporting application won't accept the datetimeoffset(7) format. Problem is I am trying to get this field to show up in UK format. I am using CAST to get my results. I can get the format I need by doing a CONVERT but my reporting system will not work with a varchar field and display the dates.
I am looking to get my results shown as dd-mm-yyyy hh:mm:ss or dd-mm-yyyy hh:mm:ss.ms
CAST statements:
CAST (dq.CreatedOn AS SMALLDATETIME) AS CreatedOn,
CAST (dq.CreatedOn AS DATETIME) AS CreatedOn2,
CONVERT statements: These won't work for my reporting system.
CONVERT(VARCHAR(30), (CAST (dq.CreatedOn AS SMALLDATETIME)), 29), -- 18-11-2025 10:21:00.000
CONVERT(VARCHAR(30), (CAST (dq.CreatedOn AS DATETIME)), 29), -- 18-11-2025 10:21:22.770
Any help would be appreciated.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16299 More actions · Answer 1

SSMS will show up whatever format is defined by you/ your locale.

your reporting software is what needs to do the formatting to UK format, not your sql, neither what you may see in SSMS. so if it only deals with a datetime datatype, that is what you need to cast the column as, nothing else.

tylerschuler75 SSC Enthusiast Points: 117 More actions · Answer 2

Sorry we are not using SSMS. we use a third party report generator called Dev express and they don't have any format options so I just need to get the format proper in SQL so I can report on that field properly. Can't do a text field or custom but must be a datetime format.

frederico_fonseca SSCoach Points: 16299 More actions · Answer 3

does not matter what tool you use - you talk about a "reporting application" - as it does not take a text field, then datetime is what you use - and datetime has NO FORMAT - what decides the display format is your reporting application.

tylerschuler75 SSC Enthusiast Points: 117 More actions · Answer 4

Thanks for that. I was trying everything. Appreciate your time.

Phil Parkin SSC Guru Points: 247206 More actions · Answer 5

Is that this Dev Express?

https://docs.devexpress.com/WindowsForms/2141/common-features/formatting-values/format-specifiers

It seems to have a comprehensive formatting capability.

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 6

I hope I'm not too late... simple conversions of DATETIMEOFFSET(7) to DATETIME will cause rounding up to the next unit of time once out of every 600 times... and that not only includes rounding up to the next second but, depending on the original value, could cause the DATETIME value to round up to the next minute, hour, day, week, month, quarter, or even YEAR!

DECLARE @DTO_7 DATETIMEOFFSET(7) = '2025-12-31 23:59:59.9983334';
 SELECT  Original = @DTO_7
        ,DATETIME_Value = CONVERT(DATETIME,@DTO_7)
;

Results: (Notice the result is in the next YEAR due to rounding!)
Original                           DATETIME_Value
---------------------------------- -----------------------
2025-12-31 23:59:59.9983334 +00:00 2026-01-01 00:00:00.000

Here's the fix for the conversion... it let's things round except for the last 1/600th of every second, which is good for both performance and expected accuracy (as good as it gets for DATETIME).

DECLARE @DTO_7 DATETIMEOFFSET(7) = '2025-12-31 23:59:59.9983334';
 SELECT  Original = @DTO_7
        ,DATETIME_Value = CONVERT(DATETIME
                                 ,IIF(DATEPART(ns,@DTO_7) > 998333300 --If in last 1/600th of a second
                                     ,DATEADD(ns,-1666700,@DTO_7) --Subtracts 1/600th of a second
                                     ,@DTO_7)) -- Otherwise, just correctly rounds the original value.
;

Results: (Prevents rounding ONLY in last 1/600th of each second for performance and accuracy)
Original                           DATETIME_Value
---------------------------------- -----------------------
2025-12-31 23:59:59.9983334 +00:00 2025-12-31 23:59:59.997

Of course, I've not taken the offset into account... you can do that. I just wanted to alert you to the possible catastrophic silent error.

Results with fix in place:

This reply was modified 5 days, 15 hours ago by Jeff Moden. Reason: Forum software kept destroying the post

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)