In SQL Server 2008 and earlier version used the CONVERT() function to handle the date formatting. As we notice that the function is not very user friendly.
In SQL Server 2012, a new function named FORMAT() has been introduced which is much easier to use.
First of all we have look about CONVERT() function and then we are going to new SQL Server 2012 FORMAT() function.
Some example of CONVERT() function is mentioned bellow
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) -- Oct 2 2010 11:01AM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.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)
-- Oct 2 2010 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
-- yyyymmdd - ISO date format - international standard - works with any language setting
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
-- 02 Oct 2010 11:02:07:577
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 - ISO8601
-- 2010-10-02T10:52:47.513
SELECT convert(varchar, getdate(), 127) -- yyyy-mm-ddThh:mm:ss.mmmZ - with time zone
SELECT convert(nvarchar(64), getdate(), 130) -- Arabic Hijri date, 15 جمادى الثانية 1433 2:40:09:210PM
SELECT convert(nvarchar, getdate(), 131) -- Arabic Hijri date-Islamic calendar, 15/06/1433 2:40:39:240PM
-- Without century (YY) date / datetime conversion - there are exceptions!
SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1) -- mm/dd/yy
SELECT convert(varchar, getdate(), 2) -- yy.mm.dd
SELECT convert(varchar, getdate(), 3) -- dd/mm/yy
SELECT convert(varchar, getdate(), 4) -- dd.mm.yy
SELECT convert(varchar, getdate(), 5) -- dd-mm-yy
SELECT convert(varchar, getdate(), 6) -- dd mon yy
SELECT convert(varchar, getdate(), 7) -- mon dd, yy
SELECT convert(varchar, getdate(), 8) -- hh:mm:ss
SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10) -- mm-dd-yy
SELECT convert(varchar, getdate(), 11) -- yy/mm/dd
SELECT convert(varchar, getdate(), 12) -- yymmdd
SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) -- hh:mm:ss
SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm
-- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm
In SQL Server 2012 the FORMAT function
In SQL Server 2012 introduced a new function named FORMAT to handle formatting dates. It is similar to ORACLE to_date() function.
The syntax of the FORMAT() function is mentioned bellow.
FORMAT(value , format [,culture])
Example of FORMAT()
SELECT FORMAT (getdate(), 'dd-MM-yy') as date -- 05-06-12
SELECT FORMAT (getdate(), 'hh:mm:ss') as time -- 2:48:42
With Culture Option
Culture is the another option of the FORMAT() function. By this we can obtain the regional formatting.
Example of USA format
SELECT FORMAT (getdate(), 'd', 'en-us') as date -- 6/5/2012
Example of Spanish culture in Bolivia
SELECT FORMAT (getdate(), 'd', 'es-bo') as date -- 5/6/2012
More Examples
Query
|
Sample output
|
select FORMAT (getdate(), 'dd/MM/yyyy ') as date
|
06/05/2012
|
select FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date
|
06/05/2012, 11:33:13
|
select FORMAT (getdate(), 'dddd, MMMM, yyyy') as date
|
Wednesday, May, 2012
|
select FORMAT (getdate(), 'MMM dd yyyy') as date
|
May 06 2012
|
select FORMAT (getdate(), 'MM.dd.yy') as date
|
05.06.12
|
select FORMAT (getdate(), 'MM-dd-yy') as date
|
05-06-12
|
select FORMAT (getdate(), 'hh:mm:ss tt') as date
|
11:36:14 AM
|
select FORMAT (getdate(), 'd','af') as date
|
2012/05/06
|
Hope you like it.
SEQUENCE in SQL 2012
SQL Server 2012 code name "Denali" introduced a new feature of T-SQL to make task easier. It is called Sequence. It generates a sequence of number.
In previous version we can specify identity fields in a table. But if we want to have database wide sequential number, then we must derive something by our self before SQL server 2012.
It was a long request by SQL community and Microsoft release it with version of SQL Server 2012.
The syntax is mentioned bellow:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
A partial list of the output demonstrates the default values.
start_value
|
-9223372036854775808
|
increment
|
1
|
mimimum_value
|
-9223372036854775808
|
maximum_value
|
9223372036854775807
|
is_cycling
|
0
|
is_cached
|
1
|
current_value
|
-9223372036854775808
|
Example to create Sequence is mentioned bellow.
CREATE SEQUENCE mysequence
START WITH 1
INCREMENT BY 1;
GO
BEGIN TRAN
SELECT NEXT VALUE FOR dbo.mysequence
ROLLBACK TRAN
Another example with Table objects are mentioned bellow.
----Create Sequence Object
CREATE SEQUENCE my_seq
START WITH 1
INCREMENT BY 1;
----Create Temp Table
DECLARE @Customer_dtl TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
----Insert Some Data
INSERT @Customer_dtl (ID, FullName)
VALUES (NEXT VALUE FOR my_seq, 'Joydeep Das'),
(NEXT VALUE FOR my_seq, 'Sudip Das'),
(NEXT VALUE FOR my_seq, 'Subarata Kar');
----Show the Data
SELECT * FROM @Customer_dtl;
The "NEXT VALUE FOR" T-SQL key words is used to get the next sequential number from sequence
Hope you like it.
SPACE() Function in SQL
Some of my friends has a lot of confusions about the functions SPACE() and it's implementations.
The SPACE function takes Integer as parameters and Returns a specified number of spaces as string.
The number of space it returns is what number you provide in the parameters.
The syntax is
SPACE(expression int)RETURN varchar
EXAMPLE
SELECT fname +','+SPACE(2)+lname 'Student Name'
FROM tbl_student
Student Name
RAJA, KUMAR
DIA, MIRZA
The Spce came in between , and Last name.
