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.
| Author |
Topic |
|
lisateg
Starting Member
10 Posts |
 Posted - 2012-02-17 : 01:51:21
|
| I'm working from a book that gives syntax for Oracle, but I'm using SQL Server. I'm trying to write a query that lists the names and ages of customers by deriving this info from the customer date of birth column and the actual date. The command I have that apparently works in Oracle is this:SELECT CUST_LNAME, CUST_FNAME, ROUND((DATE()-CUST_DOB)/365.25,0) AS AGEFROM CUSTOMER;When I use this command in SQL Server, I get the error message: Msg 195, Level 15, State 10, Line 1'DATE' is not a recognized built-in function name.What syntax should I be using to derive an age from the actual date?Thanks! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-02-17 : 01:52:29
|
use current_timestamp or getdate()
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
lisateg
Starting Member
10 Posts |
Posted - 2012-02-17 : 01:58:09
|
| Hm, getdate() gives me this message:Msg 257, Level 16, State 3, Line 1Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.And current_timestamp gives me this:Msg 102, Level 15, State 1, Line 1Incorrect syntax near '('.Though if I mouse over the 'ROUND' then I get the message that the ROUND function requires 2 to 3 arguments. I don't know what that means.ETA: I took away the parenthesis after current_timestamp (I left them there at first) and I get this message:Msg 257, Level 16, State 3, Line 1Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-02-17 : 02:10:46
|
[code]select ROUND ( datediff(day, CUST_DOB, getdate() ) / 365.25, 0 ) from CUSTOMER[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
lisateg
Starting Member
10 Posts |
Posted - 2012-02-17 : 02:14:29
|
| Awesome! That works! Thanks so much! |
|
|
|
|
|
|
|
|
Write a query to derive an age