SQL Date Function... Help me plz... :(

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

General SQL Server Forums

New to SQL Server Programming

SQL Date Function... Help me plz... :(

Author

Topic

rhoze
Starting Member

2 Posts

Posted - 2010-01-07 : 05:02:20

CREATE DATABASE Midterm1
CREATE TABLE Exercise1
(
[ID] int,
[Name] varchar(10),
[Birthday] varchar(20)
)
INSERT INTO Exercise1 VALUES(101, 'Alex', '1/20/1978')
INSERT INTO Exercise1 VALUES(102, 'Bill', '3/13/1956')
INSERT INTO Exercise1 VALUES(103, 'Ben', '7/7/1997')
INSERT INTO Exercise1 VALUES(104, 'Donna', '8/21/2009')
INSERT INTO Exercise1 VALUES(105, 'Clark', '5/11/2003')
INSERT INTO Exercise1 VALUES(106, 'Dhana', '7/12/1991')
INSERT INTO Exercise1 VALUES(107, 'Mhon', '8/8/1988')

SELECT GetDate()
SELECT IsDate('5/11/2003') as "MM/DD/YY"
SELECT Year(GetDate()) as "Year"

DatePart Abbreviation Example
year yy, yyyy DATEPART(yy, getdate())
quarter qq, q DATEPART(qq, getdate())
month mm, m DATEPART(mm, getdate())
dayofyear dy, y DATEPART(dy, getdate())
day dd, d DATEPART(dd, getdate())
week wk, ww DATEPART(wk, getdate())
weekday dw DATEPART(dw, getdate())
hour hh DATEPART(hh, getdate())
minute mi DATEPART(mi, getdate())
second ss DATEPART(ss, getdate())
millisecond ms DATEPART(ms, getdate())

--1.) How old is alex today?
--2.) What is the age difference of Ben & Donna?
--3.) WHat is the Sum of Clark, Dhana, & Mhon's age?
--4.) What is the age difference of Clark to the sum of Donna & Mhon's age?
--5.) Find the name & Age of the youngest in the table?

This is our activity lately...
I have solve the first two questions:

--1.)
SELECT [Name], Birthday, DATEDIFF(YEAR, Birthday, GetDate()) as [Age Today] FROM Exercise1 where [Name] = 'Alex'

--2.)
SELECT DATEDIFF(YEAR, (SELECT BirthDay FROM Exercise1 Where [name] = 'Ben'), (SELECT BirthDay FROM Exercise1 Where [name] = 'Donna')) as [AGE Difference]

But the problem is that I could not get the rest of the sql commands right. I don't know what to do next. I've been trying it a couple of times and I just couldn't get it right..

Plz help..

PS: I know that this is an activity but I just can't get it right...Also if you have a shorter version of my answer on my 2nd question, plz do let me know.
tnx..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 05:25:00

age is generally calculated as follows


CREATE FUNCTION GetAge
(@DOB datetime
) RETURNS int 
AS
BEGIN
DECLARE @Age int

SELECT @Age = CASE WHEN MONTH(@DOB) > MONTH(GETDATE()) OR MONTH(@DOB)= MONTH(GETDATE()) AND DAY(@DOB)> DAY(GETDATE()) THEN DATEDIFF(yy,@DOB,GETDATE()) -1 ELSE  DATEDIFF(yy,@DOB,GETDATE()) END

RETURN @Age
END

then use it like

1.SELECT [Name], Birthday, dbo.GetAge(Birthday) as [Age Today] FROM Exercise1 where [Name] = 'Alex'
2.SELECT ABS((SELECT dbo.GetAge(BirthDay) FROM Exercise1 Where [name] = 'Ben')- (SELECT dbo.GetAge(BirthDay)  FROM Exercise1 Where [name] = 'Donna')) as [AGE Difference]
3. SELECT SUM(dbo.GetAge(Birthday)) FROM Exercise1 where [Name] IN ( 'Clark', 'Dhana', 'Mhon')
4.SELECT ABS((SELECT dbo.GetAge(BirthDay) FROM Exercise1 Where [name] = 'Clark')- (SELECT SUM(dbo.GetAge(Birthday))  FROM Exercise1 Where [name] = IN (  'Donna', 'Mhon'))) as [AGE Difference]
5.SELECT [Name], dbo.GetAge(Birthday) as [Age Today] 
FROM Exercise1 t
JOIN (select MIN(dbo.GetAge(Birthday) AS MinAge FROM Exercise1) t1
ON t1.MinAge = dbo.GetAge(t.Birthday)

rhoze
Starting Member

2 Posts

Posted - 2010-01-07 : 05:26:52

w0w... such an advance method..
but I'll try and understand it. The teacher just wanna use the date function 'coz that's what the topic is all about.
And thanks!.. :D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-07 : 05:28:24

Read this article to tell your teacher that using DATEDIFF only is not reliable.
http://www.sqlteam.com/article/datediff-function-demystified

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources