sp that select a table and calculate one filed

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)

sp that select a table and calculate one filed

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2002-06-07 : 05:21:02

i want to create a sp that select a table. In one of the filed is the user's D.O.B. Is it possible to calcualte is to present it as age?

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 07:12:03

execute this as a concept

select datediff(mm,'02/01/2001',getdate())

replace the '02/01/2001' with the field that contains his DOB and integrate it into your query.
the mm = months
dd = days
yy = years.

yy is probably what you want to use.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-06-07 : 07:26:48

1 point to note....and it needs repeating as it comes up here time and time again....watch your date format....

is '02/01/2001'....the 2nd january 2001 or 1st feb 2001....???
it'll be the first in the uk...and the 2nd in the usa....

2 points....
1...it is always advisable to use the SET DATEFORMAT DMY (or MDY) statement at the top of every SP.
2...it is advisable that you use yyyy-mm-dd hh:mm:ss....as this format is ALWAYS interpreted correctly....

HTH
Andrew

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-07 : 07:40:10

Point taken.

The explicit date was an example so it doesn't matter if it is jan/feb.

Then database will know what format DOB is stored as and will interpret it correctly.

I'll remember point 2.
Thanks.

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2002-06-08 : 01:36:27

thx a lot. I will try it later when i'm back to office.

BTW, 2 more questions to ask.

1. i know that it's not possible to write sth like
iif([field1] = 'A', 'Active', 'Inactive')
so, how can i achieve the same reason?
I think a solution to use union all statement to first select the field with field1 = 'A' and mark it as 'Active' and union antoher one with 'inactive'
any better solution?

2. one of the field is too long to display in the web page. can i check the field's length and make it sth like
xxxxxxxxxxxxxxxx xxxxx + '...' if the field is longer than specific length?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-08 : 07:56:31

Yes to both:

1. SELECT CASE field1
WHEN 'A' THEN 'Active'
ELSE 'Inactive' END
FROM myTable

2. SELECT CASE
WHEN Len(field1)>50 THEN Left(field1, 50) + '...'
ELSE field1 END
FROM myTable

Take a look in Books Online for the "CASE" expression, it's extremely handy. Also check these:

http://www.sqlteam.com/item.asp?ItemID=922
http://www.sqlteam.com/redir.asp?ItemID=3223

Hell, check ALL of these:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=case

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2002-06-08 : 12:44:45

thx a lot for your reply. I read the link u provide and learned a lot from it. a thousand thx. it helps me a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources