Conversion Question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Conversion Question

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-14 : 14:30:38

In MS Access I have this line of SQL that tells the current age of people. It subtracts todays date from date of birth and formats the number:

DateDiff('yyyy',[tbl_OFFICER].[DateOfBirth],Now())+Int(Format(Now(),'mmdd')<Format([tbl_OFFICER].[DateOfBirth],'mmdd')) AS AGE,

How would I write this in SQL for SQL Server?

Any help is much appreciated..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-14 : 14:42:23

datediff(yy,[tbl_OFFICER].[DateOfBirth],Now())...

but then what is ...+Int(Format(Now(),'mmdd')<Format([tbl_OFFICER].[DateOfBirth],'mmdd')) for?

Corey

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 14:52:43

I presume that evaluates to TRUE or FALSE which in turn adjusts the "Age" by one, according to whether the birthday is before, or after, todays "day" within the year.

Kristen

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-14 : 15:00:50

Formats the dates to SELECT datediff(yy,[tbl_OFFICER].[DateOfBirth],Now()) AS AGE
FROM tbl_Officer;

returns

Server: Msg 195, Level 15, State 10, Line 1
'Now' is not a recognized function name.

"but then what is ...+Int(Format(Now(),'mmdd')<Format([tbl_OFFICER].[DateOfBirth],'mmdd')) for?"

To format years...

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-14 : 15:02:01

What it needs to do is calculate the age of a person.

In layman terms.

Todays Date - Birth Day = 50

or 50 years old.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-14 : 15:04:52

oops.. my bad

Select datediff(yy,[tbl_OFFICER].[DateOfBirth],getdate())...

Corey

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-14 : 15:20:34

That works great.. Thank you Corey....

Ok one more.

Its an IF statement for a field

IIf(IsNull([tbl_Position].[Room]),'',[RM_ROOM]) AS NEW_ROOM

Basically if the field is null bring back nothing else bring back the field.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-14 : 15:25:59

look up Case statements in BOL

example:

Declare @color nvarchar(100)
Set @color = 'blue'
Select FavoriteColor = case when @color = 'Blue' then 1 else 0 end
Set @color = 'green'
Select FavoriteColor = case when @color = 'Blue' then 1 else 0 end

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-14 : 15:33:03

note that ISNULL() in Access is different from ISNULL() in SQL.

- - - - clip 'n' save - - - - - - - - - - - - -


Access      T-SQL                          Description
------      ---------                      ------------
ISNULL(x)   x is null                      Returns TRUE if X is null
NZ(x,val)   ISNULL(x,val)                  Returns Val is x is null; otherwise returns x
IIF(b,a,c)  CASE WHEN b THEN a else C END  Returns a if b is true; otherwise returns c

- - - - - - - - - - - - - - - - - - - - - - - -

- Jeff

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-14 : 15:51:30

OK here is what I have but not working..

SELECT [PositionName], [City],
CASE [ROOM]
WHEN NULL
THEN 'No Room'
ELSE [ROOM]
END AS [ROOM]
FROM tbl_Position

If the field is Null I would like it to say 'No Room'... It keeps returning a NULL value instead.

I don't mean to ask so many questions especially on some things that may seem awefully simple. But I truley appreciate your help.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-14 : 15:52:49

Select [PositionName], [City], isnull([Room],'No Room') As [Room]
From tbl_Position

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-14 : 15:53:48

by the way... BOL (Books On Line - aka T-SQL Help) is your friend

Corey

JimAmigo
Posting Yak Master

119 Posts

Posted - 2004-07-14 : 16:07:11

That will work. Just for learning purposes.. couldn't the same task been done with the Case Statement?