extract month & year from date column

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

extract month & year from date column

Author

Topic

phanicrn
Starting Member

42 Posts

Posted - 2008-02-06 : 11:32:01

hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 12:00:17

Is it a datetime field?

phanicrn
Starting Member

42 Posts

Posted - 2008-02-06 : 12:10:38

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 12:16:37

use MONTH(datefield) & YEAR(datefield) function or DATEPART(yy,datefield) & DATEPART(mm,datefield)

phanicrn
Starting Member

42 Posts

Posted - 2008-02-06 : 12:20:37

hi
How do i write a calculation, anything before jan 1st 1995 should be considered as dec 31 and 1994 . If i have data from 1910 to 1994, all the account opened dates should sit in this bucket..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 12:28:01

CASE WHEN datefield < 'Jan 1st 1995' THEN 'Dec 31st 1994'
WHEN datefield > 'Jan 1st 2100' THEN 'Jan 1st 2011'
END

phanicrn
Starting Member

42 Posts

Posted - 2008-02-06 : 12:36:42

Intially i have columns account_open_date , data as Jan 1 1995 in column..

now i want write a calculation saying .. anything before the date jan 1 1995 should sit under dec 31 and 1994 in this bucket. and after jan 2 1995, i should get the reamining account open dates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:32:21

CASE WHEN account_open_date < 'Jan 1st 1995' THEN 'Dec 31st 1994'
ELSE account_open_date END

phanicrn
Starting Member

42 Posts

Posted - 2008-02-06 : 13:37:58

little bit work,
cast(account_open_Date,varchar(10)) gives me 2005-10-10
that's it
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:41:01

why are you converting it to varchar? its already in datetime format.

phanicrn
Starting Member

42 Posts

Posted - 2008-02-06 : 13:56:39

if i dont do, it's not filtering my data..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-07 : 07:52:44

quote:
Originally posted by phanicrn

little bit work,
cast(account_open_Date,varchar(10)) gives me 2005-10-10
that's it
thanks

Dont convert dates to varchars
To omit time part, use

dateadd(day,datediff(day,0,account_open_Date),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources