New DATETIME functions in Denali

New DateTime  Function in Denali

1. EOMONTH
    This will return last day for the month from the date at specified offset
   
    syntax
   
    EOMONTH ( start_date [, month_to_add ] )
 

   
    StartDate :- Date from which we want to get last day of month at offset
   
    Offset:- Specifying number of months to add. Default is 0
   
   
    Example
   
  

SELECT Eomonth(Getdate()) last_day_of_month

   
   

   
   
    with offset
   
   
    select eomonth (getdate(), 1) last_day_next_month, eomonth(getdate(),-1) last_day_prev_month
   
   

2.DATEFROMPARTS       

Returns day from specified value of year,  month, day

DATEFROMPARTS ( year, month, day )

Example

SELECTDatefromparts (2011, 09, 01)

3.TIMEFROMPARTS

Returns a time from specified time paratmeters

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Precision shows part of second
if its 7 its shows nano second , if its 3 it shows millisecond

Fraction is depending on precision.its length should  be in range of precision

SELECTTimefromparts (10, 10, 10, 0, 0) AS time

With specifying fraction

SELECTTIMEFROMPARTS (10, 10, 10, 1, 7 ) AS Result

Lag:-New function in sql server denali

LAG

We all have read about LEAD in previous blog . LAG is working opposite of LEAD where we can go backword in dataset.

We can get value from previous subsequent row with given offset value without using a self join or CTE

We can use this function to compare  value with other row in the Table for analysis purpose

Syntax

LAG ( scalar_expression ,offset , default )

    OVER ( Parition By,Order By)

Scalar Expression :- Any scalar expression allowed in SQL Server

Offset:- Number of rows forward from the current row

Default:- Default value if no row find at given Offset location  from current row

Example

Lets see we want to compare order quantity of each workorder with its previous workorder

SELECT Lag(orderqty, 1, 0) OVER (ORDER BY workorderid) nextorderqty,

       orderqty,

       *

FROM   production.workorder

Whate we can see here is that first row does not have any pervious subsequent row. so it will display defualt 0

Use of Partition by function

Now we want to compare order quantity but in each ProductID

SELECT Lag(orderqty, 1, 0) OVER (partition BYproductid ORDER BYworkorderid) nextorderqty,

       orderqty,

       *

FROM   production.workorder

Here first order in each ProductID  will have default value 0 because it doesnt have next record in the same ProductID