Formatting date using the T-SQL Convert() function

Date is of datatype datetime and you can format it to a string. Date as used in different countries is formatted differently some may use month name, some use hyphens between date, month and year while others use slashes, etc.

The syntax of the Convert() function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The optional attributes are as usual within []. The data_type and expression are required. The style is where you can look for adoption to the format (different countries use differently). While length is optional, it should be adequately chosen.

The expression is converted and the resulting data_type with specified length will be displayed.

The queries for this post were run in Microsoft SQL Operations Studio (sqlops). One can only query in SQL Operations studio after being connected to one of the SQL Server instances

Here is just Select GetDate() query.

The formatting style is of ISO 8601 (126)

The Japanese 4 and 2-digit year formats.

--4 digit year

----2 digit year

The British and French style

The Hijiri style as in Islamic Countries. Note this may be at slight variance with this site:

https://www.islamicfinder.org/islamic-date-converter/

And finally the default for datatime/smalldatetime

Differences between datetime and datetime2 formats in SQL Server

You can evaluate GetDate() function in SQL Server 2012 to see the default format of datetime as shown.

Date related data in SQL Server 2012 by default is of datetime format. There are two types of datatime in SQL Server, the datetime and datetime2. The date data type does not have any information on time. You can create a column with datatype time or datetime or datetime2 as shown.

Now, what are the differences between datetime and datetime2?

The syntax is different; the date range is different and dattime2 can have higher number of decimal seconds.

Syntax: datetime
date range: Jan 1, 1753 to Dec 31, 9999
Time range:00:00:00 through 23:59:59.997
Default value:1900-1-1 00:00:00


Syntax: datetime2[fractional seconds]
date range: 0001-01-01 through 9999-12-31
Time range:00:00:00 though 23:59:59.0000000
default fractional seconds 7
Default value:1900-01-01 00:00:00

Here is an example of using datetime2:

The ISO 8601 format is: 2004-05-23T14:25:10.487
where T is start of time format