New Date and Time Functions in SQL Server 2012

New Date and Time Functions in SQL Server 2012

 

SQL Server 2012, code named Denali, has introduced some new DATE and TIME Functions. In this post, we will discuss on how these new functions are used.

Here is the list of New DATE and TIME Functions

 

-       DATEFROMPARTS

-       TIMEFROMPARTS

-       DATETIMEFROMPARTS

-       DATETIME2FROMPARTS

-       SMALLDATETIMEFROMPARTS

-       DATETIMEOFFSETFROMPARTS

-       EOMONTH

DATEFROMPARTS 

 

The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default as shown in the below query result.

 

DECLARE @YEAR  INT = 2012,
        @MONTH INT = 1,
        @DAY   INT = 1

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [Result]
GO

Result
----------
2012-01-01 

 

TIMEFROMPARTS

 

The TIMEFROMPARTS function, returns a full time value as shown in the below query result.

It is important to note that the fractions argument actually depends on the precision argument.

 

For example:

 

When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.

 

·         When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.

·         When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

 

DECLARE @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59

SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]
GO

Result
------------
11:59:59.500 

 

DATETIMEFROMPARTS

 

The DATETIMEFROMPARTS function,  returns a full datetime value as shown in the below query result.

 

DECLARE @YEAR         INT = 2012,
        @MONTH        INT = 1,
        @DAY          INT = 9,
        @HOUR         INT = 11,
        @MINUTE       INT = 59,
        @SECONDS      INT = 59,
        @MILLISECONDS INT = 0

SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS,
       @MILLISECONDS)
       AS [Result]
GO

Result
-----------------------
2012-01-01 11:59:59.000 

DATETIME2FROMPARTS

 

The DATETIME2FROMPARTS function, returns a full datetime2 value as shown in the below query result.

 

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59

SELECT  
  DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3)
  AS [Result]
GO

Result
-----------------------
2012-01-01 11:59:59.500 

 

SMALLDATETIMEFROMPARTS

 

The SMALLDATETIMEFROMPARTS function, which is available in SQL Server 2012, returns a full smalldatetime value as shown in the below query result.

 

DECLARE @YEAR   INT = 2012,
        @MONTH  INT = 1,
        @DAY    INT = 1,
        @HOUR   INT = 11,
        @MINUTE INT = 59

SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Result]
GO

Result
-------------------
2012-01-01 11:59:00 

 

DATETIMEOFFSETFROMPARTS

 

The DATETIMEOFFSETFROMPARTS function, returns a full datetimeoffset data type as shown in the below query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

 

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59

SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS,
       500, 5, 30, 3) AS [Result]
GO

Result
------------------------------
2012-01-01 11:59:59.500 +05:30 

 

EOMONTH

 

The EOMONTH function, calculates the last date of the month based on the date which is passed as an input parameter.

 

DECLARE @STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE) AS [Last Date of Month]
GO

Last Date of Month
-----------------------
2012-01-31 00:00:00.000 

 

TSQL script - CTE to remove duplicate rows

TSQL script - CTE to remove duplicate rows

// Creating Table and Inserting Data //

CREATE TABLE #Table (C1 INT,C2 VARCHAR(10))

INSERT INTO #Table VALUES (1,'SQL Server')

INSERT INTO #Table VALUES (1,'SQL Server')

INSERT INTO #Table VALUES (2,'Oracle')

// Retrieving Data //

SELECT * FROM #Table


-- Delete Duplicate rows --
  
;WITH Delete_Duplicate_Row_cte

AS (SELECT ROW_NUMBER()OVER(PARTITION BY C1, C2 ORDER BY C1,C2) ROW_NUM,*

FROM #Table )

DELETE FROM Delete_Duplicate_Row_cte WHERE ROW_NUM > 1

-- Retrivieng Records after deleting duplicate rows --

SELECT * FROM #Table

 -- Drop the table --
DROP TABLE #Table

T-SQL - CUMULATIVE SUM

T-SQL   -  CUMULATIVE  SUM 

There are several ways to calculate sum in T – SQL

Here, I will show how to calculate cumulative sum in T-Sql using row_number function.

Let’s take an example where we have to calculate student wise cumulative sum of marks.

Script to create a table:

CREATE TABLE #tblMarks(

    studid  VARCHAR(20)

    , subcode VARCHAR(20)

    , marks   INT

)

 

 

Populating Data into the table:

INSERT INTO #tblMarks

SELECT 'Stud1', 'English', 60 UNION ALL

SELECT 'Stud1', 'History', 70 UNION ALL

SELECT 'Stud1', 'Maths', 80 UNION ALL

SELECT 'Stud1', 'Science', 75 UNION ALL

SELECT 'Stud2', 'English', 55 UNION ALL

SELECT 'Stud2', 'History', 60 UNION ALL

SELECT 'Stud2', 'Maths', 57 UNION ALL

SELECT 'Stud2', 'Science', 65

 

Retrieving Data from the table #tblMarks

 

SELECT * FROM #tblMarks

 

 

 

 

Query to calculate cumulative sum for all subjects student wise

;WITH cte AS (

    SELECT  row_number() OVER (ORDER BY studid, subcode) AS rownum,*

    FROM    #tblMarks

)

 

SELECT  a.studid, a.subcode, a.marks, SUM(b.marks) AS [Cumulative Sum]

FROM    cte a

LEFT JOIN cte b ON a.studid = b.studid AND b.rownum <= a.rownum

GROUP   BY a.studid, a.rownum, a.subcode, a.marks

ORDER   BY a.studid, a.subcode



 

 

Output :

 

 

T-SQL Script to list Object Dependencies

T-SQL script to list object dependencies

 

-- SQL Server 2008 object dependency query - listing object dependencies

 

SELECT ReferencingObjectType = o1.type,

       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,

       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,

       ReferencedObjectType = o2.type

FROM   sys.sql_expression_dependencies ed

       INNER JOIN   sys.objects o1

         ON ed.referencing_id = o1.object_id

       INNER JOIN sys.objects o2

         ON ed.referenced_id = o2.object_id

WHERE o1.type in ('P','TR','V', 'TF')

ORDER BY ReferencingObjectType, ReferencingObject