equivalent of SET DATEFORMAT DMY that can be used in a function

Question

equivalent of SET DATEFORMAT DMY that can be used in a function

aaron.reese

SSChampion

Points: 13498
More actions
November 18, 2022 at 4:11 pm

#4114910

I have a table function that needs to strip some text from a string and see if the remaining string is a valid date. (Don't ask... crappy database design and application over which I have no control!)
The problem is that we are in the UK and the database/instance has been set up to default to MDY (US format) so when I run isDate('30/09/2022') I get 0 :(. It works if I do
SET DATEFORMAT DMY
SELECT isDate('30/09/2022')
But when I put the SET DATEFORMAT DMY into the table function I get the error message
Invalid use of a side-effecting operator 'SET COMMAND' within a function
I can strip the date down into its parts and recompile it, but I wondered if there was a more elegant solution. If not I will likely throw it into a utility function so that the parser can be more flexible.

Viewing 5 posts - 1 through 4 (of 4 total)

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

ratbak Ten Centuries Points: 1328 More actions · Answer 1

If you know the dates are always in DD/MM/YYYY format, perhaps you could pre-swap the month and day segments of the string -- e.g.,

DECLARE @DateString CHAR(10) = '30/09/2022'
SELECT CONCAT(SUBSTRING(@DateString,4,2),'/',LEFT(@DateString,2),'/',RIGHT(@DateString,4))

ratbak Ten Centuries Points: 1328 More actions · Answer 2

If you know the dates are always in DD/MM/YYYY format, perhaps you could pre-swap the month and day segments of the string & avoid the need to set dateformat -- e.g.,

DECLARE @DateString CHAR(10) = '30/09/2022'
SELECT CONCAT(SUBSTRING(@DateString,4,2),'/',LEFT(@DateString,2),'/',RIGHT(@DateString,4))

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 3

Why not use TRY_CONVERT - if it comes back as NULL then it isn't a valid date. I assume you know that the date format will always be DD/MM/YYYY and you don't have mixed US and British formats.

Select try_convert(datetime, '30/09/2022', 103)
     , try_convert(datetime, '30/09/2022', 101);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

aaron.reese SSChampion Points: 13498 More actions · Answer 4

I am actually trying to write a data quality trap becasue I need to report the date to a government agency. If the date eists in the correct format it will be day/month/year but I can't trust the users to always do dd/mm/yyyy it could be 7/6/23 or 07-06-2023 7th June 2023 or any other variation

At this stage I only need to check if IS a date, I don't need to know what the date is. (I will do later though)

I'll have a go at the TRY_CONVERT on monday.