You are right, the date format is different between the servers.
Lots of people fall into the trap of assuming that if you specify a date literal as 'YYYY-MM-DD', it will be interpreted as that regardless of the current date format. This is incorrect. SQL Server sees the 4 digits at the start of the string and correctly deduces that they represent the year. However, it then uses the current date format to tell which way round the month and day are. If you are in the UK, for example, this puts you in an awkward situation because you need a date format of DMY to interpret a date literal like 'DD-MM-YYYY', but a date format of MDY to interpret a date literal like 'YYYY-MM-DD'.
You have several options:
SET DATEFORMAT YMD, and don't let users enter dates any other way.- Use the ODBC date literal syntax
{d'YYYY-MM-DD'}. This will be parsed correctly regardless of the current date format.CONVERT(DATE, 'YYYY-MM-DD', 120)has the same effect. - Remove all literal values from your queries and use parameters instead. This is by far the best alternative, and I strongly recommend it.
MSSQLTips
mssqltips.com › home › sql date format examples using convert function
SQL Date Format Examples using SQL CONVERT Function
September 26, 2025 - In this article, we look at how to use the SQL CONVERT function to format dates and times in over 100 different formats. ... SQL Server provides a number of date and time formatting options and in this article we look at how to use SQL CONVERT to output different date/time formats such as mm/dd/yy, mm/dd/yyyy, dd-mm-yyyy, etc.
Stack Overflow
stackoverflow.com › questions › 51411931 › t-sql-convert-datetime-to-varchar-using-style-20-vs-120
sql server - T-SQL CONVERT datetime to varchar using style 20 vs 120 - Stack Overflow
The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy). ... SELECT CONVERT(VARCHAR(10), GETDATE(), 20) AS 'exampleOne' SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS 'exampleTwo'
W3Schools
w3schools.com › sql › func_sqlserver_convert.asp
SQL Server CONVERT() Function
String Functions: ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Date Functions: CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME MS Access Functions
DataSunrise
datasunrise.com › home › sql server datetime formats: a comprehensive guide
SQL Server Datetime Formats: A Comprehensive Guide
July 17, 2025 - -- Store once in UTC, present it locally DECLARE @utc DATETIME2 = SYSUTCDATETIME(); SELECT @utc AS StoredUTC, @utc AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS BerlinTime, @utc AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS SeattleTime; Stick to ISO styles (120/121) for APIs, reserve locale-specific formats for presentation layers, and always store in UTC when rows travel across regions. Microsoft SQL Server is a relational database platform used widely across enterprise environments.
Tutlane
tutlane.com › article › sql-server › convert-format-datetime-in-sql-server-with-examples
Convert (Format) DateTime in SQL Server with Examples - Tutlane
Here is an example of how to convert a datetime value to a different format using the CONVERT function in the SQL server. SELECT CONVERT(VARCHAR(30), GETDATE(), 120) ------------------------------------------- 2023-03-21 07:52:59
Top answer 1 of 4
2
You are right, the date format is different between the servers.
Lots of people fall into the trap of assuming that if you specify a date literal as 'YYYY-MM-DD', it will be interpreted as that regardless of the current date format. This is incorrect. SQL Server sees the 4 digits at the start of the string and correctly deduces that they represent the year. However, it then uses the current date format to tell which way round the month and day are. If you are in the UK, for example, this puts you in an awkward situation because you need a date format of DMY to interpret a date literal like 'DD-MM-YYYY', but a date format of MDY to interpret a date literal like 'YYYY-MM-DD'.
You have several options:
SET DATEFORMAT YMD, and don't let users enter dates any other way.- Use the ODBC date literal syntax
{d'YYYY-MM-DD'}. This will be parsed correctly regardless of the current date format.CONVERT(DATE, 'YYYY-MM-DD', 120)has the same effect. - Remove all literal values from your queries and use parameters instead. This is by far the best alternative, and I strongly recommend it.
2 of 4
2
is you use different formats for the string then you can avoid this behaviour.
There are 2 iso formats that are always specific -- sql server will always parse them in the same way regardless of the server date format setting.
These are:
1) Short form : YYYYMMDD. Example '20120301' -- 1st March 2012
2) Long Form : YYYY-MM-DDTHH:MM:SS.msms'. Example '2012-03-01T12:13:00.000Z' -- 1st March 2012 at 13 minutes past 12 (PM)
In the long form the miliseconds is optional -- this is a perfectly acceptable ISO datetime '2012-03-01T12:13:00Z'
The Z at the end is time zone information. SQL Server doesn't actually require this. (though other products are a bit more exacting)
Try this for example:
DECLARE @foo DATETIME
SET DATEFORMAT DMY
-- this will be the 3rd of january in DMY
SET @foo = '2012-03-01'
SELECT 'DMY: Not ISO', @foo
SET @foo = '20120301'
SELECT 'DMY: ISO', @foo
SET DATEFORMAT MDY
-- this will be the 1st of March in MDY
SET @foo = '2012-03-01'
SELECT 'MDY: not ISO', @foo
SET @foo = '20120301'
SELECT 'MDY: ISO', @foo
When you use text to enter dates you should always try to use one of the two ISO standards. It just makes things much more deterministic.
Short format (SQL Server) http://msdn.microsoft.com/en-US/library/ms187085(v=sql.90).aspx
ISO 8601 Format (SQL Server) http://msdn.microsoft.com/en-us/library/ms190977(v=sql.90).aspx
Database Guide
database.guide › list-of-date-formats-available-with-convert-in-sql-server
List of Date Formats Available with CONVERT() in SQL Server
January 12, 2021 - The following table contains a list of the date formats that you can provide to the CONVERT() function when you convert a date/time value to a string.
C# Corner
c-sharpcorner.com › article › convert-datetime-to-different-formats-in-sql-server
Mastering the Art of Convert DateTime Formats In SQL Server with Over 30 Code Examples
October 4, 2023 - By using format code as 114 we can get datetime in “HH:mm:ss: fff” format. ... By using format code as 120 we can get datetime in “YYYY-MM-DD HH:mm: ss” format.
Top answer 1 of 16
510
SELECT CONVERT(char(10), GetDate(),126)
Limiting the size of the varchar chops of the hour portion that you don't want.
2 of 16
180
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
Experts Exchange
experts-exchange.com › articles › 12315 › SQL-Server-Date-Styles-formats-using-CONVERT.html
SQL Server Date Styles (formats) using CONVERT() | Experts Exchange
October 3, 2013 - declare @Your_Data_Here as datetime ... then 'YY MM DD' when starts = 'MMM' and charindex('2001',Styled_Date) > 1 then 'MMM DD YYYY' when starts = 'MMM' and charindex('2001',Styled_Date) < 1 then 'MMM DD YY' when starts = ...
Tutorial Gateway
tutorialgateway.org › sql-date-format
SQL DATE Format using Convert, Format Functions
March 23, 2025 - Here, we will use the DATETIME functions that are available to format date and time in SQL Server to return the date in different formats. SELECT DATEFROMPARTS(2017, 06, 14) AS 'Result 1'; SELECT DATETIMEFROMPARTS(2017, 06, 14, 11, 57, 53, 847) AS 'Result 3'; SELECT EOMONTH('20170614') AS 'Result 3'; In this example, we are going to use the Conversion Functions to format the Date and Time.
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 1660795 › handling-different-date-formats-in-azure-sql
Handling different date formats in Azure SQL - Microsoft Q&A
April 26, 2024 - To be more precise and cover some ... formats (103 and 120) used in SQL Server and converts them to the desired format ('MM/dd/yyyy hh:mm:ss')....
MSSQLTips
mssqltips.com › home › format sql server dates with format function
Format SQL Server Dates with FORMAT Function
October 31, 2025 - Format SQL Date examples using SQL FORMAT Function to format dates, time and numbers in SQL Server with valuable code.
Sql-server-helper
sql-server-helper.com › tips › date-formats.aspx
SQL Server Helper - Tips and Tricks - Date Formats
SQL Server Date Formats · One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › functions › cast-and-convert-transact-sql
CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
SQL Server supports the date format, in Arabic style, with the Kuwaiti algorithm. 1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles. 2 The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, 23, and 21 or 25 or 121) always return the century (yyyy).
GoLinuxCloud
golinuxcloud.com › home › sql › sql date format explained with examples
SQL Date Format Explained with Examples | GoLinuxCloud
January 13, 2024 - Different types and formats of SQL date format explained with examples. SQL datetime format syntax, type and examples. sql server date format.
DB Vis
dbvis.com › thetable › the-ultimate-guide-to-the-sql-server-date-format
The Ultimate Guide to the SQL Server Date Format
October 23, 2024 - The default SQL Server DATE format is yyyy-MM-dd, as you can verify with the following query: ... This calls the GETDATE() function to retrieve the current date and time, and then uses an SQL cast to convert the result to DATE.
Wise Owl
wiseowl.co.uk › sql › guides › dates-and-times › formatting_dates_and_times_in_sql_server
Formatting Dates and Times in SQL Server
There's much more that you can do with the Format function in SQL, should the above have piqued your interest! The CONVERT function is normally used to change one data type into another but it also has the ability to format dates. Technically it does this by converting a date into one of the text data types although that isn't really too important. All you need to know is that the function has this basic structure: CONVERT(Data type you want, Value to convert, Date style code) The simple example below changes a range of dates into the UK standard "DD/MM/YY" format:
Coffingdw
coffingdw.com › how-to-format-dates-in-sql-server-over-1800-examples-included
How to Format Dates in SQL Server – Over 1800 Examples Included – Software connecting all databases
January 18, 2024 - This blog will brilliantly break down and explain everything about the formatting dates in any way you desire on an Azure Synapse or SQL Server system. I will include over 1800 examples to avoid confusion about formatting dates. Azure Synapse and SQL Server use two ways to format dates: the CONVERT and FORMAT functions to transform raw dates as you want them presented.
QA With Experts
qawithexperts.com › article › sql › sql-server-date-format-and-converting-it-various-examples › 158
SQL server date format and converting it (Various examples) - QA With Experts
April 10, 2022 - This article provides you in-depth knowledge of SQL server date format available and how to convert from one date format to other using examples