year datepart - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

year datepart

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-22 : 12:18:14

how to convert year without hard coded the year:

2012-datepart(year,DateOfBirth)

else every year need to change the year(2012,2013,2014,...)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 12:37:41

you mean this?


..
DATEDIFF(yy,DateOfBirth,GETDATE())
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 12:45:35

If you are trying to calculate age, simply taking the difference of the current year and the year of the date of birth would not work correctly. You can calculate the age using the following:

SELECT
(
   CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
   - 
   CAST(CONVERT(CHAR(8), DateOfBirth, 112) AS INT)
)/10000

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-22 : 13:12:36

Thanks sunitabeck

How if i can set any date to get last week date also using datediff?

Todays's date to get last week output..

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-22 : 13:13:05

Thanks sunitabeck

How if i can set any date to get last week date also using datediff?

Todays's date to get last week output..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 16:25:39

quote:
Originally posted by peace

Thanks sunitabeck

How if i can set any date to get last week date also using datediff?

Todays's date to get last week output..

last week last or end date?

last week start date would be

DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)

last week end date would be
DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1

see logic here

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 19:13:45

If you are trying to calculate the date exactly one week ago today, one or the other of the following will do it. The first one gives you the date and time, second simply gives the date without the time part.

SELECT DATEADD(dd,-7,GETDATE());

SELECT DATEADD(dd,DATEDIFF(dd,7,GETDATE()),0);

The dateadd function is a very useful function to calculate various things: http://msdn.microsoft.com/en-us/library/ms186819.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources