Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using DateDiff to calculate days before a Birthday Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 12:52 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:06 PM
Points: 887, Visits: 2,453
Revenant (9/7/2010)
The correct way is to celebrate a Feb 29th birthday on the 28th, that's how the Roman (Catholic) calendar does it. In fact, it is not the 29th that is added on leap years: it is a second Feb 24th and the following four days of February are "bumped up."


The correct way to calculate age is to follow whatever regulations affect your industry or the operational definition of "age" specific to your business goal.

In the U.S., at least, different agencies calculate leap day baby ages differently.
Post #981825
Posted Tuesday, September 7, 2010 1:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Of course. I should have said "In absence of any industry rules or guidelines, ..."

However, the Common Law still says that if a debt is due on February 24th of a leap year, it may be paid on 25th (the second, inserted 24th).
Post #981848
Posted Friday, September 10, 2010 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, Visits: 64
Jeff Hi,

No matter what I try, I cannot get your code snippet to work, is there any possibilty you could expand it to show its used with code and using northwind.sdf. If I take out the --Today shown in Green in your code and having assumed it may be a comment it tells me the function Year is not recognised by the Compact Edition. When left in it gives an error parsing the query at Line 1 position 166 error = ,

But I cannot get rid of the error.
Post #983667
Posted Friday, September 10, 2010 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, Visits: 64
Jeff Hi,

No matter what I try, I cannot get your code snippet to work, is there any possibilty you could expand it to show its used with code and using northwind.sdf. If I take out the --Today shown in Green in your code and having assumed it may be a comment it tells me the function Year is not recognised by the Compact Edition. When left in it gives an error parsing the query at Line 1 position 166 error = ,

But I cannot get rid of the error.
Post #983668
Posted Friday, September 10, 2010 5:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 35,552, Visits: 32,149
Sorry, Ken... I was a bit asleep at the switch and forgot this was for the compact edition (heh... "CE" actually stands for "Crippled Edition", doesn't it? )

There's more than one way to do the same thing so, let's check...

What do you get in CE when you run each of these individually?

SELECT YEAR(GETDATE())

SELECT DATEPART(yy,GETDATE())


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #983672
Posted Friday, September 10, 2010 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, Visits: 64
Jeff,
Year = A similiar error, Error Parsing Line 1 at 29 error = (
DatePart = error Parsing line 1 at 33

I am using Northwind.sdf Employees table which has a Birthdate field for the example

cmd.CommandText = "SELECT * FROM Employees YEAR(GETDATE())"
cmd.CommandText = "SELECT * FROM Employees DATEPART(yy,GETDATE())"
Post #983724
Posted Friday, September 10, 2010 7:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, Visits: 64
Jeff,
Just entering SELECT YEAR(GETDATE()) give the error Function is not recognized by SQL Server Compact Edition of Function [Year]

Running DATEPART returned 2010
Post #983733
Posted Friday, September 10, 2010 7:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 35,552, Visits: 32,149
kenkob (9/10/2010)
Jeff,
Just entering SELECT YEAR(GETDATE()) give the error Function is not recognized by SQL Server Compact Edition of Function [Year]

Running DATEPART returned 2010


Then doing a little substitution in my orginal query should do the trick... (I'll let you change the DB name/schema if necessary)

 SELECT *
FROM AdventureWorks.HumanResources.Employee
WHERE DATEDIFF(dd,
DATEADD(yy, -(DATEPART(yy,GETDATE())-1900),GETDATE()), --Today
DATEADD(yy, -(DATEPART(yy,BirthDate)-1900),BirthDate)
) BETWEEN 1 AND 10



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #984217
Posted Sunday, September 12, 2010 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 13, 2012 5:31 AM
Points: 18, Visits: 64
Jeff,

After a little trouble I have got it working. When I attempted to use it as a SqlCe CommandText string surrounded by inverted commas, I was always getting an error. However, by adding the code copied directly from the web forum page into a multiline textbox it worked fine.
Thank you very much for your help.
Post #984402
Posted Sunday, September 12, 2010 9:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 35,552, Visits: 32,149
kenkob (9/12/2010)
Jeff,

After a little trouble I have got it working. When I attempted to use it as a SqlCe CommandText string surrounded by inverted commas, I was always getting an error. However, by adding the code copied directly from the web forum page into a multiline textbox it worked fine.
Thank you very much for your help.


Thanks for the feedback, Ken. I usually end up pasting the copy from the forum into MS Word which "fixes" a lot of stuff from forums and then copy/paste to my application. Almost the same thing as you did.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #984491
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse