Date function - Error in sql

Question

I have a members table with field DataNa type date.

I would like to make a query like:

Select DateDiff (year, datana, dateserial (year,12,31)) as years, ....
from members ... etc ...

Year is a parametric value. But the system tells me that DateSerial is not a recognized value of default function name.

I also tried to do:

Select DateDiff (year, datana, datevalue ('31/12/' + year)) as years, ....
from members ... etc ...

It says it also is not a DateValue recognized default function name

I'm using SQL Server 2008R2

What do you want to accomplish?

jpw
– jpw

2013-08-05 09:07:49 +00:00
Commented Aug 5, 2013 at 9:07 — jpw
– jpw, Commented Aug 5, 2013 at 9:07
what format is year ?

t-clausen.dk
– t-clausen.dk

2013-08-05 09:08:30 +00:00
Commented Aug 5, 2013 at 9:08 — t-clausen.dk
– t-clausen.dk, Commented Aug 5, 2013 at 9:08

peterm · Accepted Answer · 2013-08-05 09:19:58Z

2

Are you looking for something like this?

SELECT DATEDIFF(YEAR, datana, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1)) years
  FROM members

This part

DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1)

returns last day of the current year (e.g. 2013-12-31)

Here is SQLFiddle demo

answered Aug 5, 2013 at 9:19

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

t-clausen.dk Over a year ago

so why would you pick the last date of last year ? It doesn't matter which date of the year you pick when using datediff(year, x,y)

Nenad Zivkovic · Accepted Answer · 2013-08-05 09:19:32Z

1

If you want to create 31-12-YYYY out from @Year int parameter for YYYY - you can do it like this:

DATEADD(DD,-1,DATEADD(YY,@year - 1899,0))

But if you want to get difference in years between two dates - it's not important to have it 31.12, 1.1 of same year will give identical result, which you can get with:

SELECT DATEDIFF (YEAR, datana, DATEADD(YY,@year - 1900,0))

SQLFiddleDEMO

answered Aug 5, 2013 at 9:19

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

2 Comments

Antony Over a year ago

Tanks far all. But the DateSerial function there is SqlServer2088R2??

Nenad Zivkovic Over a year ago

@Antony No. Such function does not exits. SQL Server 2012 introduced something similar with DATEFROMPARTS function, but in 2008R2 you have to use alternatives like shown.

Collectives™ on Stack Overflow

Date function - Error in sql

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related