Home > SQL Server > Change the Date format of the current language – SET DATEFORMAT – SQL SERVER

Change the Date format of the current language – SET DATEFORMAT – SQL SERVER


Formatting date is one of the mandatory task for the developers to show date values in the user reports. There are various ways to format date in SQL Server database and reporting services. But, in this blog post I am going to show you how to set up the date format that user is going to use for the date value with rearranging the date part string (dmy) in SQL Server.

This setting only applies to date data type (date, smalldatetime, datetime, datetime2 and datetimeoffset) string value in an expression.

DateFormat set statement also overrides the date format defined in the default language settings. Learn about Language in SQL Server

Set statement syntax

SET DATEFORMAT { format | @format_var }

Format or @format variableĀ  must contain day (d), month (m) and year(y) combination format. For instance mdy, dmy, ymd, ydm, myd, and dym.

There are some restrictions applies to specific DateFormat format to use a date value stored in date, datetime2 and datetimeoffset data types. Because, we cannot use ydm format to use the date value.

pic1A valid expression

pic2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: