Below Script gives you three important details related to date using datepart() function.
Current Week of the Year
Start of Week Date
End of Week Date
when we will execute the below script we will get the output like below:-
SELECT DATEPART( WeeK, SYSDATETIME()) 'Current Week of the Year', CONVERT(VARCHAR(15), DATEADD(dd, -(DATEPART(dw, SYSDATETIME())-1), SYSDATETIME()),101) 'Start of Week Date', CONVERT(VARCHAR(15), DATEADD(dd, 7-(DATEPART(dw, SYSDATETIME())), SYSDATETIME()),101) 'End of Week Date'
I wrote this to calculate your age by years,months,days
DECLARE @age DATETIME, @TEMPDATE DATETIME, @YEARS INT, @MONTHS INT, @DAYS INT
SET @age = –put datetime here in yyyy-mm-dd format—-
SELECT @TEMPDATE = @age
SELECT @YEARS = DATEDIFF(YEAR, @TEMPDATE, GETDATE()) –
CASE
WHEN (MONTH(@age) > MONTH(GETDATE()) OR
(MONTH(@age) = MONTH(GETDATE()) AND DAY(@age) > DAY (GETDATE())))
THEN 1 ELSE 0
END
SELECT @TEMPDATE = DATEADD(YEAR, @YEARS, @TEMPDATE)
SELECT @MONTHS = DATEDIFF(MONTH, @TEMPDATE, GETDATE()) –
CASE
WHEN DAY(@age) > DAY(GETDATE())
THEN 1 ELSE 0
END
SELECT @TEMPDATE = DATEADD(MONTH, @MONTHS, @TEMPDATE)
SELECT @DAYS = DATEDIFF(DAY, @TEMPDATE, GETDATE())
SELECT @YEARS AS [YEARS], @MONTHS AS [MONTHS], @DAYS AS [DAYS]