Skip to main content

Datediff Function Sql Server

By
DateDiff function returns the  difference between to dates

Suppose we have employee table and it has following rows and columns
Id
Name
Description
JoiningDate
1
Test1
Executive
10/5/2013 0:00
2
Test2
S.Executive
10/5/2012 0:00
3
Test3
Team Lead
10/5/2012 0:00
4
Test4
Jr Engineer
10/11/2014 0:00


Calculate the Employee joining Year, month and days
  select name, datediff(year,JoiningDate,getdate()) Year  , datediff(year,JoiningDate,getdate()) month ,datediff(DAY,JoiningDate,getdate()) day from Employee  where joiningdate is not null

Calculate the age   Sql server in years-month and days

  Declare @dateofbirth datetime
  Declare @years int
  declare @months int
  declare @days int
  declare @temp date
  set @dateofbirth='1983-04-27';
  set @temp  =@dateofbirth

  set @years = (select DATEDIFF(yy, @temp, GETDATE())- CASE WHEN (MONTH(@dateofbirth) > MONTH(GETDATE())) OR (MONTH(@dateofbirth) = MONTH(GETDATE()) AND DAY(@dateofbirth) > DAY(GETDATE())) THEN 1 ELSE 0 END)

SET @temp = DATEADD(yy, @years, @temp)

SET @months = DATEDIFF(m, @temp, GETDATE()) - CASE WHEN DAY(@dateofbirth) > DAY(GETDATE()) THEN 1 ELSE 0 END

SET @temp = DATEADD(m, @months, @temp)
SET @days = DATEDIFF(d, @temp, GETDATE())

select @years as TotalYear ,@months as M

Comments

Post a Comment

buy cloth

Popular posts from this blog

Left & Right Outer Join Example in LINQ using Lambda Expression

By
Left & Right Outer Join Example in LINQ using  Lambda Expression Combines two collections into a single hierarchical collection. The join operation is based on matching keys. This is called group join so lambda expression we can use the group join to achieve the result Suppose we have two classes’ person and Address public   class   Person {      public   int  EmpId {  get ;  set ; }      public   string  FirstName {  get ;  set ; }      public   string  LastName {  get ;  set ; } } public   class   Address {      public   int  AddressId {  get ;  set ; }      public   int  EmpId {  get ;  set ; }      public   string  CityName {  get ;  set ; }      public   strin...
Post a Comment
Read more

Ajax based Shopping cart example in jquery Asp.net

By
In this example we have show basic functionally of shopping cart using ajax. So we have three classes  named  shopping cart, shopping cart model, product. Product class contain the three propertiesproductId, productname, unitprice as shown given below public   class   Product {      public   int  ProductId {  get ;  set ; }      public   string  ProductName {  get ;  set ; }      public   decimal  UnitPrice {  get ;  set ; } } Here shopping class contain the info  productid, productname ,quantity , unitprice, total price . TheseInfo store in shopping cart class public   class   ShoppingCart {      public   int  ProductId {  get ;  set ; }      public   string  ProductName {  get ;  set ; }      public   ...
Post a Comment
Read more

Fluent nhibernate MVC Sample

By
This article describing the how to implement the Fluent nhibernate. What first you have to know what is fluent nhibernate in .net? What is NHibernate and fluent nhibernate in .net? First you have to know about NHibernate. it is an object-relational mapping (ORM) solution for the Microsoft .NET platform. It provides a framework for mapping an object-oriented domain model to a traditional relational database. Why would you need it? Because it can save you from writing a lot of tedious ADO.NET code. Essentially it enhances developer productivity when developing CRUD applications, that is, applications whose main purpose is to Create, Read, Update, and Delete data in a database. NHibernate is open source, and you need to realize that you are making your application dependent on third party libraries, whose long term goals may diverge from yours. In NHibernate we need to create mapping purely xml based. We need to create xml  mapping  to map the table wher...
Post a Comment
Read more