{"id":204,"date":"2014-08-21T09:45:25","date_gmt":"2014-08-21T09:45:25","guid":{"rendered":"http:\/\/mairwa.com\/wordpress\/?p=204"},"modified":"2014-08-21T09:45:25","modified_gmt":"2014-08-21T09:45:25","slug":"find-datedifference-in-fomat-of-number-of-yearmonth-and-days-in-sql-server","status":"publish","type":"post","link":"http:\/\/mairwa.com\/wordpress\/?p=204","title":{"rendered":"Pivot table and Find DateDifference in fomat of Number of year,Month and Days in sql server"},"content":{"rendered":"

—-Use of datedifference<\/strong><\/p>\n

Declare @PreviousDate <\/span>datetime<\/span><\/span>=<\/span><\/span>‘2001-11-08’<\/span><\/span>;<\/span><\/span><\/p>\n

Declare<\/span><\/span> @CurrentDate <\/span>date<\/span><\/span>=<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>date<\/span><\/span>,<\/span><\/span>GETDATE<\/span><\/span>(),<\/span><\/span>110<\/span>);<\/span><\/span><\/p>\n

Declare<\/span><\/span> @CurrentDate1 <\/span>date<\/span><\/span>=<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>varchar<\/span><\/span>(<\/span><\/span>10<\/span>),<\/span><\/span>GETDATE<\/span><\/span>(),<\/span><\/span>110<\/span>);<\/span><\/span><\/p>\n

declare @datediff <\/span>int<\/span><\/span>=<\/span><\/span>DATEDIFF<\/span><\/span>(<\/span><\/span>DAY<\/span><\/span>,<\/span><\/span>@PreviousDate<\/span>,<\/span><\/span>@CurrentDate<\/span>);<\/span><\/span><\/p>\n

Declare @NumLeapYear <\/span>INT<\/span><\/span>=<\/span><\/span>DATEDIFF<\/span><\/span>(<\/span><\/span>YEAR<\/span><\/span>,<\/span><\/span>@PreviousDate<\/span>,<\/span><\/span>@CurrentDate<\/span>)\/<\/span><\/span>4 –for Count of Leap Year but may be some extend to be need.<\/span>;<\/span><\/span><\/p>\n

with FindDate <\/span>as<\/span><\/span>(<\/span><\/span>Select<\/span><\/span> @datediff <\/span>as<\/span><\/span> TotalDays<\/span>,<\/span><\/span><\/p>\n

(<\/p>\n

 <\/p>\n

@datediff <\/span>%<\/span><\/span> 365<\/span>)<\/span><\/span>as<\/span><\/span> NumMonth<\/span>,<\/span><\/span>–for calculate of MonthCount<\/span><\/span><\/p>\n

@CurrentDate<\/p>\n

as<\/span><\/span> TodayDate<\/span>,<\/span><\/span>@CurrentDate1 <\/span>as<\/span><\/span> TodayDate1 <\/span>)<\/span><\/span><\/p>\n

Select<\/p>\n

 <\/p>\n

TotalDays<\/span>\/<\/span><\/span>365 <\/span>as<\/span><\/span>Year<\/span><\/span>,<\/span><\/span><\/p>\n

NumMonth<\/p>\n

\/<\/span><\/span>31 <\/span>as<\/span><\/span>Month<\/span><\/span>,<\/span><\/span><\/p>\n

(((<\/span><\/span>NumMonth<\/span>\/<\/span><\/span>31<\/span>)%<\/span><\/span>31<\/span>)+<\/span><\/span>@NumLeapYear<\/span>)<\/span><\/span>as<\/span><\/span>days<\/span><\/span>,<\/span><\/span>TotalDays <\/span>as<\/span><\/span> TotalDays<\/span>,<\/span><\/span>@CurrentDate <\/span>as<\/span><\/span> TodayDate<\/span>,<\/span><\/span>@CurrentDate1 <\/span>as<\/span><\/span> TodayDate1<\/span><\/p>\n

from<\/span><\/span> FindDate<\/span>;<\/span><\/span><\/p>\n

–Use of pivot table<\/strong><\/p>\n

select<\/p>\n

 <\/p>\n

*<\/span><\/span>from<\/span><\/span><\/p>\n

(<\/p>\n

 <\/p>\n

select<\/span><\/span> permissionid<\/span>,<\/span><\/span>permissionname<\/span>,<\/span><\/span>parentpermissionid<\/span>,<\/span><\/span>level<\/span><\/span>,<\/span><\/span>isparent<\/span>,<\/span><\/span>dependentpermissionid<\/span>,<\/span><\/span>sortorder <\/span>from<\/span><\/span> Permission<\/span>)<\/span><\/span>as<\/span><\/span> s<\/span><\/p>\n

pivot<\/p>\n

(<\/p>\n

count<\/p>\n

 <\/p>\n

(<\/span><\/span>permissionid<\/span>)<\/span><\/span>for<\/span><\/span> [level] <\/span>in<\/span><\/span>(<\/span><\/span>“1”<\/span>,<\/span><\/span>“2”<\/span>,<\/span><\/span>“3”<\/span>,<\/span><\/span>“4”<\/span>))<\/span><\/span>as<\/span><\/span> d<\/span>;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

select<\/p>\n

 <\/p>\n

*<\/span><\/span>from<\/span><\/span> permission<\/span>;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

select<\/p>\n

 <\/p>\n

*<\/span><\/span>from<\/span><\/span><\/p>\n

(<\/p>\n

 <\/p>\n

select<\/span><\/span> permissionid<\/span>,<\/span><\/span>[level] <\/span>from<\/span><\/span> Permission<\/span>)<\/span><\/span>as<\/span><\/span> s<\/span><\/p>\n

pivot<\/p>\n

(<\/p>\n

count<\/p>\n

 <\/p>\n

(<\/span><\/span>permissionid<\/span>)<\/span><\/span>for<\/span><\/span> [level] <\/span>in<\/span><\/span>(<\/span><\/span>“1”<\/span>,<\/span><\/span>“2”<\/span>,<\/span><\/span>“3”<\/span>,<\/span><\/span>“4”<\/span>))<\/span><\/span>as<\/span><\/span> d<\/span>;<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

—-Use of datedifference Declare @PreviousDate datetime=‘2001-11-08’; Declare @CurrentDate date=CONVERT(date,GETDATE(),110); Declare @CurrentDate1 date=CONVERT(varchar(10),GETDATE(),110); declare @datediff int=DATEDIFF(DAY,@PreviousDate,@CurrentDate); Declare @NumLeapYear INT=DATEDIFF(YEAR,@PreviousDate,@CurrentDate)\/4 –for Count of Leap Year but may be some extend to be need.; with FindDate as(Select @datediff as TotalDays, (   @datediff % 365)as NumMonth,–for calculate of MonthCount @CurrentDate as TodayDate,@CurrentDate1 as TodayDate1 ) Select   TotalDays\/365 asYear, NumMonth \/31 asMonth, (((NumMonth\/31)%31)+@NumLeapYear)asdays,TotalDays… Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-204","post","type-post","status-publish","format-standard","hentry","category-sql-server","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=204"}],"version-history":[{"count":0,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/204\/revisions"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=204"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}