2

I use Laravel with PHP from https://laravel.com/

MSSQL is a driver and works perfectly - with th exception of dates.

For some reason the project has decided that the follwoing is a universal date format

Y-m-d H:i:s 2021-01-21 12:00:00

However this does not seem to be universal as MSSQL interprets this as a US based date eg. The date above is invalid as 21 is considered the month not the day.

I will try to get them to allow date to be set to a custom format but this may take a while.

My MSSQL user is set to Language:British English which solves most of my issues, but not all.

Is it possible to in some way tell MSSQL to interpret a date like this

2021-01-22 12:00:00

as

YYYY-MM-DD HH:II:SS

instead of

YYYY-DD-MM HH:II:SS

which is what it is doing?

Can this be done on a system wide basis?

Improve this question
4
  • rdms sort by date so it shoud be the same dd-mm or mm-dd, but you seem to tread it like t string, why?
    – nbk
    Commented Jan 23, 2021 at 9:43
  • @nbk - I assume the framework is submitting SQL statements or procedure calls via pure SQL strings rather than prepared queries with typed parameters, so at this point the choice is out of Toby's hands.
    – David Spillett
    Commented Jan 23, 2021 at 10:58
  • @DavidSpillett he wants a systemwide sorting of dates and i can't see why see dbfiddle.uk/… also as strinh it shpould also sort like he wants without any convert necessary as long there are 2 digits days and month
    – nbk
    Commented Jan 23, 2021 at 11:49
  • @nbk - he is not asking about sorting the values, but parsing them into a date type (for storage/calculation/comparison/other).
    – David Spillett
    Commented Jan 23, 2021 at 17:15

2 Answers 2

Reset to default
2

This is caused by the user being set to "British English". I've always considered it a bug (I imagine someone was told that UK dates had month & day inverted from the US norms, and they incorrectly assumed that applied to ISO8601-a-like dates also). This has been the case since at least SQL7 IIRC. It is irritating because in every other context YYYY-MM-DD is unambiguous.

If you explicitly convert string to dates using CONVERT() you can specify the string format that is being used using its optional third parameter. This may not help if the issue is with date type parameters being sent in SQL strings (instead of prepared queries with typed parameters) as the conversion in that case is out of your hands.

If you only ever provide dates in a year-first format then just avoid the British English user setting. This may not help if your application sometimes passes in non-US year-last dates.

Improve this answer
0

"No", is the answer to the question in the title. There's no system-wide setting for interpretation of language-dependent datetime formats.

There is a connection setting (SET DATEFORMAT), which inherits from another connection setting (SET LANGUAGE), which in turn inherits from a login setting (the language for your login.

So, using language dependent datetime formats is a really really bad idea. If you have a tool or framework which does that (even if only by default), then you know that those who developed that tool/framework aren't very proficient when it comes to SQL Server.

Ideally, of course, you want (your tool) to work with parameterized queries. For several reasons, perhaps the strongest being security: https://xkcd.com/327/

In case you (r tool) can't parameterize the queries, then at least go for language independent formats. There are several of those, some formats depends in turn if you use the old or new types regarding whether they are language dependent or not.

I'v explored this (among other things) here: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.