DatePart(DatePart, Date) - Returns an integer representing the specified DatePart. This function is simialar to DateName(). DateName() returns nvarchar, where as DatePart() returns an integer. The valid DatePart parameter values are shown below.
Examples:
Select DATEPART(weekday, '2012-08-30 19:45:31.793') -- returns 5
Select DATENAME(weekday, '2012-08-30 19:45:31.793') -- returns Thursday
DATEADD (datepart, NumberToAdd, date) - Returns the DateTime, after adding specified NumberToAdd, to the datepart specified of the given date.
Examples:
Select DateAdd(DAY, 20, '2012-08-30 19:45:31.793')
-- Returns 2012-09-19 19:45:31.793
Select DateAdd(DAY, -20, '2012-08-30 19:45:31.793')
-- Returns 2012-08-10 19:45:31.793
DATEDIFF(datepart, startdate, enddate) - Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
Examples:
Select DATEDIFF(MONTH, '11/30/2005','01/31/2006') -- returns 2
Select DATEDIFF(DAY, '11/30/2005','01/31/2006') -- returns 62
Consider the emaployees table below.
Write a query to compute the age of a person, when the date of birth is given. The output should be as shown below.
CREATE FUNCTION fnComputeAge(@DOB DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @tempdate DATETIME, @years INT, @months INT, @days INT
SELECT @tempdate = @DOB
SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE()) - CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(YEAR, @years, @tempdate)
SELECT @months = DATEDIFF(MONTH, @tempdate, GETDATE()) - CASE WHEN DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(MONTH, @months, @tempdate)
SELECT @days = DATEDIFF(DAY, @tempdate, GETDATE())
DECLARE @Age NVARCHAR(50)
SET @Age = Cast(@years AS NVARCHAR(4)) + ' Years ' + Cast(@months AS NVARCHAR(2))+ ' Months ' + Cast(@days AS NVARCHAR(2))+ ' Days Old'
RETURN @Age
End
Using the function in a query to get the expected output along with the age of the person.
Select Id, Name, DateOfBirth, dbo.fnComputeAge(DateOfBirth) as Age from tblEmployees
Examples:
Select DATEPART(weekday, '2012-08-30 19:45:31.793') -- returns 5
Select DATENAME(weekday, '2012-08-30 19:45:31.793') -- returns Thursday
DATEADD (datepart, NumberToAdd, date) - Returns the DateTime, after adding specified NumberToAdd, to the datepart specified of the given date.
Examples:
Select DateAdd(DAY, 20, '2012-08-30 19:45:31.793')
-- Returns 2012-09-19 19:45:31.793
Select DateAdd(DAY, -20, '2012-08-30 19:45:31.793')
-- Returns 2012-08-10 19:45:31.793
DATEDIFF(datepart, startdate, enddate) - Returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
Examples:
Select DATEDIFF(MONTH, '11/30/2005','01/31/2006') -- returns 2
Select DATEDIFF(DAY, '11/30/2005','01/31/2006') -- returns 62
Consider the emaployees table below.
Write a query to compute the age of a person, when the date of birth is given. The output should be as shown below.
CREATE FUNCTION fnComputeAge(@DOB DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @tempdate DATETIME, @years INT, @months INT, @days INT
SELECT @tempdate = @DOB
SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE()) - CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(YEAR, @years, @tempdate)
SELECT @months = DATEDIFF(MONTH, @tempdate, GETDATE()) - CASE WHEN DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(MONTH, @months, @tempdate)
SELECT @days = DATEDIFF(DAY, @tempdate, GETDATE())
DECLARE @Age NVARCHAR(50)
SET @Age = Cast(@years AS NVARCHAR(4)) + ' Years ' + Cast(@months AS NVARCHAR(2))+ ' Months ' + Cast(@days AS NVARCHAR(2))+ ' Days Old'
RETURN @Age
End
Using the function in a query to get the expected output along with the age of the person.
Select Id, Name, DateOfBirth, dbo.fnComputeAge(DateOfBirth) as Age from tblEmployees
Sir very long back when i visited your blog, I am not sure, But i felt like i have seen javascript vedio tutorials.. If I am not wrong, please upload it again sir....
ReplyDeleteHello sir,thanks a lot for the videos....but sorry to say that some vedeos are not visible that means i am not able to see the query i.e. all are grayed out
ReplyDeleteIf the videos are not vicible properly, increse resolution of the video (right bottom cornor-->gear like symbol to 360/480)
Deleteif the persons DOB is greater than GETDATE() value then the above function will fail
ReplyDeleteyou need to check using if condition
wasim
listen brother GETDATE() is always grater then to DOB
Deleteif DOB is grater then GETDATE() means
that person is not born
that is a feature thing
if DOB and GETDATE() are same then datediff() gives zero
and IF DOB is grater then GETDATE() means age goes -ve
world knows age, energy,mass like these thing never have -ve value
Your video tutorials are of great help to any serious developer,It helps to clear concepts with clarity.
ReplyDeleteThanks for all the effort you have put up to group the tutorials in logical order.
When I am executing function dbo.fnComputeAge in the end it is showing an error as 'Arithmetic overflow error converting expression to data type nvarchar.'
ReplyDeletePlease tell me whats wrong with this
I am getting this error.. Can any one guide me how to resolve this???
ReplyDeleteMsg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.
Create Table Employee
(Id int NOT NULL primary key,
Name nvarchar(50),
DOB datetime
)
insert into Employee values ('1', 'Atique', '1986-11-14 08:26:00.000')
insert into Employee values ('2', 'Imran', '1984-10-10 03:32:00.000')
insert into Employee values ('3', 'Hassan', '1996-07-26 08:26:00.000')
insert into Employee values ('4', 'Khawar', '1990-11-02 03:32:00.000')
insert into Employee values ('5', 'Asad', '1991-01-03 03:32:00.000')
insert into Employee values ('6', 834783, '1990-11-02 03:32:00.000')
select * from Employee
Create Function ComputeEmpolyeesAge (@DOB DateTime)
Returns NVARCHAR(50)
AS
BEGIN
Declare @tempdate Datetime, @years Int, @month Int, @day Int
Select @tempdate = @DOB
Select @years = DATEDIFF (YEAR, @tempdate, GETDATE()) - Case When
(Month (@DOB) > (GETDATE())) OR MONTH(@DOB) = Month(GETDATE()) AND DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END
Select @tempdate = DATEADD (YEAR, @years, @tempdate)
Select @month =DATEDIFF (Month, @tempdate, GETDATE()) - CASE WHEN
Day(@DOB) > Day(GetDate()) THEN 1 ELSE 0 END
Select @tempdate = DATEADD(Month,@month, @tempdate)
Select @day = DATEDIFF(Day, @tempdate, @DOB)
Declare @Age Nvarchar(50)
Set @Age = Cast (@years as Nvarchar(4)) + 'Years' + cast (@month as nvarchar (2)) + 'Montbs' + Cast(@day as nvarchar(2)) + 'Days Old'
Return @Age
END
Select Name, DOB, dbo.ComputeEmpolyeesAge(convert (nvarchar,'1991-12-23 10:14:00.067')) as Age
from Employee
Pls check, getting days count in your function.
DeleteSelect @day = DATEDIFF(Day, @tempdate, @DOB)
in above code instead of @DOB you can use GETDATE()
how do i can take this query into a trigger that check the age and rollback when it's not in the range?
ReplyDeleteselect ISDATE('sunanda');--0
ReplyDeleteselect ISDATE('23:52:41.8962531');--0
select ISDATE(GETDATE());--1
select ISDATE('2018-12-15');--1
select ISDATE('12-15-2018');--1
select ISDATE('12-15-2018 23:52:41.896');--1
select ISDATE('12-15-2018 23:52:41.8964124');--0
select ISDATE('12-15-2018 23:52:41.8964124 +12:00');--0
--NOTE time,datetime2 & datetimeoffset it retuens ZERO
---------------------------------------------------------
--retuens DAY or MONTH or YEAR number of perticular date
--DAY()
select DAY('1996-04-15') --DAY 15
select MONTH('1996-04-15') --DAY 04
select YEAR('1996-04-15') --DAY 1996
select DAY(GETDATE())
select MONTH(GETDATE())
select YEAR(GETDATE())
--------------------------------------------
--returns the string that part of pericular string
--DATENAME(daypart,date)
select DATENAME(Weekday,Getdate())
select DATENAME(month,Getdate())
select DATENAME(year,Getdate())
select DATENAME(hour,Getdate())
select DATENAME(minute,Getdate())
select DATENAME(second,Getdate())
select DATENAME(millisecond,Getdate())
select DATENAME(microsecond,Getdate())
select DATENAME(nanosecond,Getdate())
select DATENAME(QUARTER,Getdate())
select DATENAME(QUARTER,'03-25-2018')
select DATENAME(QUARTER,'06-25-2018')
select DATENAME(QUARTER,'09-25-2018')
select DATENAME(QUARTER,'12-25-2018')
select DATENAME(week,'01-01-2018')
select DATENAME(week,'01-31-2018')
select DATENAME(week,'02-01-2018')
select DATENAME(week,'02-21-2018')
select DATENAME(week,'02-28-2018')
select DATENAME(week,'12-31-2018')
select DATENAME(DAYOFYEAR,'01-01-2018')
select DATENAME(DAYOFYEAR,'12-31-2018')
select DATENAME(TZOFFSET,'12-31-2018')
-----------------------------------------
--returns the integer that part of pericular string
--DATEPART(daypart,date)
select DATEPART(Weekday,Getdate())
select DATEPART(month,Getdate())
select DATEPART(year,Getdate())
select DATEPART(hour,Getdate())
select DATEPART(minute,Getdate())
select DATEPART(second,Getdate())
select DATEPART(millisecond,Getdate())
select DATEPART(microsecond,Getdate())
select DATEPART(nanosecond,Getdate())
select DATEPART(QUARTER,Getdate())
select DATEPART(QUARTER,'03-25-2018')
select DATEPART(QUARTER,'06-25-2018')
select DATEPART(QUARTER,'09-25-2018')
select DATEPART(QUARTER,'12-25-2018')
select DATEPART(week,'01-01-2018')
select DATEPART(week,'01-31-2018')
select DATEPART(week,'02-01-2018')
select DATEPART(week,'02-21-2018')
select DATEPART(week,'02-28-2018')
select DATEPART(week,'12-31-2018')
select DATEPART(DAYOFYEAR,'01-01-2018')
select DATEPART(DAYOFYEAR,'12-31-2018')
select DATEPART(TZOFFSET,'12-31-2018')
---------------------------------------
--comaprision of DatePart & Date Name
select DATENAME(month,Getdate())
select DATENAME(year,Getdate())
select DATENAME(weekday,Getdate())
select DATEPART(month,Getdate())
select DATEPART(year,Getdate())
select DATEPART(weekday,Getdate())
-------------------------------------------
--DateAdd(datepart,NumberTOadd,Add)
select DateAdd(month,10,'2018-02-21')--it will add 10 mnths 2018-12-21 00:00:00.000
select DateAdd(year,10,'2018-02-21')--it will add 10 YEARS 2028-02-21 00:00:00.000
select DateAdd(weekday,10,'2018-02-21')--it will add 10 Days 2018-03-03 00:00:00.000
select DateAdd(month,-10,'2018-02-21')--it will remove 10 mnths 2017-04-21 00:00:00.000
select DateAdd(year,-10,'2018-02-21')--it will remove 10 YEARS 2008-02-21 00:00:00.000
select DateAdd(weekday,-10,'2018-02-21')--it will remove 10 Days 2018-02-11 00:00:00.000
Hi, I am getting this error "Arithmetic overflow error converting expression to data type nvarchar."
ReplyDeleteI did exactly what this video says.
Thank you
Hi I have to calculate the financial year .. which start from 1 July and ends with 30 June ..how to calculate..
ReplyDeletecan any one explain case statement with dob(2020-09-02)
ReplyDelete