---
description: "SET DATEFORMAT (Transact-SQL)"
title: "SET DATEFORMAT (Transact-SQL) | Microsoft Docs"
ms.custom: ""
ms.date: "03/14/2017"
ms.prod: sql
ms.prod_service: "database-engine, sql-database, synapse-analytics, pdw"
ms.reviewer: ""
ms.technology: t-sql
ms.topic: reference
f1_keywords: 
  - "DATEFORMAT"
  - "SET DATEFORMAT"
  - "SET_DATEFORMAT_TSQL"
  - "DATEFORMAT_TSQL"
dev_langs: 
  - "TSQL"
helpviewer_keywords: 
  - "dates [SQL Server], formats"
  - "dates [SQL Server], ordering date parts"
  - "SET DATEFORMAT option [SQL Server]"
  - "DATEFORMAT option [SQL Server]"
  - "date and time [SQL Server], SET DATEFORMAT"
  - "options [SQL Server], date"
  - "date and time [SQL Server], DATEFORMAT"
  - "dateparts [SQL Server], dateformat"
ms.assetid: da217878-7ec4-477e-aa13-604073c948f8
author: WilliamDAssafMSFT
ms.author: wiassaf
monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current"
---
# SET DATEFORMAT (Transact-SQL)
[!INCLUDE [sql-asdb-asdbmi-asa-pdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]

  Sets the order of the month, day, and year date parts for interpreting date character strings. These strings are of type **date**, **smalldatetime**, **datetime**, **datetime2**, or **datetimeoffset**.  
  
 For an overview of all [!INCLUDE[tsql](../../includes/tsql-md.md)] date and time data types and functions, see [Date and Time Data Types and Functions (Transact-SQL)](../../t-sql/functions/date-and-time-data-types-and-functions-transact-sql.md).  
  
 ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)  
  
## Syntax  
  
```syntaxsql
SET DATEFORMAT { format | @format_var }   
```  
  
[!INCLUDE[sql-server-tsql-previous-offline-documentation](../../includes/sql-server-tsql-previous-offline-documentation.md)]

## Arguments
 *format* | **@**_format_var_  
 Is the order of the date parts. Valid parameters are **mdy**, **dmy**, **ymd**, **ydm**, **myd**, and **dym**. Can be either Unicode or double-byte character sets (DBCS) converted to Unicode. The U.S. English default is **mdy**. For the default DATEFORMAT of all support languages, see [sp_helplanguage (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-helplanguage-transact-sql.md).  
  
## Remarks  
 The DATEFORMAT **ydm** isn't supported for **date**, **datetime2**, and **datetimeoffset** data types.  
  
 The DATEFORMAT setting may interpret character strings differently for date data types, depending on their string format. For example, **datetime** and **smalldatetime** interpretations may not match **date**, **datetime2**, or **datetimeoffset**. DATEFORMAT affects the interpretation of character strings as they're converted to date values for the database. It doesn't affect the display of date data type values, nor their storage format in the database.  
  
 Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.  
  
 The setting of SET DATEFORMAT is set at execute or run time and not at parse time.  
  
 SET DATEFORMAT overrides the implicit date format setting of [SET LANGUAGE](../../t-sql/statements/set-language-transact-sql.md).  
  
## Permissions  
 Requires membership in the **public** role.  
  
## Examples  
 The following example uses different date strings as inputs in sessions with the same `DATEFORMAT` setting.  
  
```sql
-- Set date format to day/month/year.  
SET DATEFORMAT dmy;  
GO  
DECLARE @datevar DATETIME2 = '31/12/2008 09:01:01.1234567';  
SELECT @datevar;  
GO  
-- Result: 2008-12-31 09:01:01.123  
SET DATEFORMAT dmy;  
GO  
DECLARE @datevar DATETIME2 = '12/31/2008 09:01:01.1234567';  
SELECT @datevar;  
GO  
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.  
  
GO  
```  
  
## See Also  
 [SET Statements (Transact-SQL)](../../t-sql/statements/set-statements-transact-sql.md)