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 9 posts - 1 through 9 (of 9 total)

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

frederico_fonseca SSCoach Points: 16306 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: 16306 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: 247215 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: 1004701 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 4 weeks, 1 days 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)

semolina Newbie Points: 7 More actions · Answer 7

You cannot force a UK dd-mm-yyyy format in SQL if your reporting tool only accepts DATETIME. DATETIME itself has no format; it’s just a binary date-time value. The display format is controlled by your reporting software.

If you’re worried about rounding errors when converting from DATETIMEOFFSET(7) to DATETIME, you can use Jeff Moden’s approach to prevent the rare rounding-up issues:

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,
DATEADD(ns,-1666700,@DTO_7),
@DTO_7));

This avoids rounding errors while keeping the value as DATETIME. For UK formatting in reports, configure DevExpress (or your reporting tool) to display dd-MM-yyyy hh:mm:ss. SQL can’t enforce that on a DATETIME column.

frederico_fonseca SSCoach Points: 16306 More actions · Answer 8

its getting tiresome that you @semolina keep posting same information prior posts already gave, just for the sake of showing off. if you don't have new information to supply refrain from posting.