What's new in SQL server 2012 - Date time

What's new in SQL server 2012 - Date time

                                                Date & Time is main area when we talk about database or database development. I have discuss many issue in SQL Server Data & time data type. if you need to read it again then following blog post will reach you there, 

• ISDATE Function
• Minimum year value

 well, today I'm going to discuss about SQL Server 2012 newly added Date & time related features.Before start this topic hope you will notice I have discuss about ISDATE function in my above blog link. As a solution of SQL Server 2012 development team will introduce following new functions, 

• TRY_CONVERT

                          This is very good move of SQL Server 2012 because T-SQL has very poor data validation,those issue will be a good solution above Try_convert function.   

USE tempdb
GO
SELECT CASE WHEN TRY_CONVERT(int, '1,200.00') IS NULL THEN 'False' ELSE 'True' END AS ResultOut;
GO
SELECT CASE WHEN TRY_CONVERT(datetimeoffset, '2013-11-08 23:59:59.3593750 +05:30') IS NULL THEN 0 ELSE 1 END AS is_date;
GO

• TRY_PARSE 

                      This function also more likely above Try_convert function. 

USE tempdb
GO
SELECT TRY_PARSE('100' AS int) AS ResultOut;
GO
SELECT TRY_PARSE('100.1' AS int) AS ResultOut;
GO
SELECT TRY_PARSE('100.0' AS int) AS ResultOut;
GO

                    only two different i have notice above both functions as follows,  

• Try_parse only allows string as input.
• try_parse just parse values but try_convert convert the values like this,

SELECT TRY_PARSE('100.0' AS int) AS ResultOut;  --True
GO
SELECT TRY_CONVERT(int,'100.0') AS ResultOut; --Error
GO

• PARSE

                This also more likely TRY_PARSE function. only different is if values not match then it will returns error,

USE tempdb
GO
SELECT PARSE('100' AS int) AS ResultOut; --True
GO
SELECT PARSE('100.1' AS int) AS ResultOut; --False
GO
SELECT PARSE('€345,98' AS money USING 'de-DE') AS ResultOut;
GO
SELECT PARSE('345,98' AS money USING 'ja-JP') AS ResultOut;
GO

• DATEFROMPARTS 

                               This is very simple function, it will concatenate year , month and day. And validate it too,

USE tempdb
GO
SELECT DATEFROMPARTS ( 2013, 11, 30 ) AS ResultOut;
GO
SELECT DATEFROMPARTS ( 2013, 11, 31 ) AS ResultOut;--error
GO
SELECT DATEFROMPARTS ( 2012, 02, 29 ) AS ResultOut;
GO
SELECT DATEFROMPARTS ( 2013, 02, 29 ) AS ResultOut;--error
GO

• DATETIME2FROMPARTS , DATETIMEFROMPARTS & DATETIMEOFFSETFROMPARTS 

                                          These functions mostly same as above function, after seen of Syntax you will realize it,

Syntax         
   

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

           

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

 
Following example will help you more clearly,

USE tempdb
GO
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS ResultOut;
GO
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS ResultOut;
GO
SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS ResultOut;
GO

• EOMONTH 

                     This is one of my favourite function.Earlier SQL Server versions if you need to get end of the moth I have used following SQL statement,

USE tempdb
GO
DECLARE @date datetime= '11/20/2013';
SELECT DATEADD (mm,1,@date-(DAY(@date)-1))-1;
GO

  But now you have no need to write above painful SQL statements you can use as follows,

USE tempdb
GO
DECLARE @date datetime= '11/20/2013';
SELECT EOMONTH ( @date ) AS ResultOut;
GO