Wednesday, March 28, 2018

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:



And finally the default for datatime/smalldatetime










No comments:

Post a Comment

What is SQLOISIM? What is it used for in SQL Server?

SQLIOSIM is a tool for simulating SQL Server IO. SQLIOSIM performs reliability and integrity tests on the disk systems that SQL Server uti...