update query using Birthdays. Microsoft SQL Server 2000 2

[ja01]

I ran a query to show an id and birthday as follows:
select id,birthd,brthyr,brthmn,brthdy

from dbo.core
where id= '0000000'

The results are id=00000000, birthd=1877-01-01, birthyr=null, brthmn=null and brthdy=null

I want to take the birthd and populate it into the three null tables. Thus I want the following:
birthyr=1877, brthmn=01 and brthdy=01

I produced a partial update query
update dbo.core
set birthyr = ???
where id = '0000000'
How difficult would it be to pull out the birthyr in this case. I guess I would need a separate update query for the month and day.

 

[gmmastros]

Here's one way...

update dbo.core
set    birthyr = Year(BirthD),
       brthmn = Month(BirthD),
       brthdy = Day(BirthD)
where  id = '0000000'

Personally, I think that is doesn't make sense to store the year, month and day seperately when you are already storing this as a date.

Instead, you should forget about this columns and just calculate the values like I show. For example, suppose you want to get all the birthdays in March.

Ex:

[tt][blue]
Select *
From dbo.code
Where Month(birthd) = 3
[/blue][/tt]

If you want to show the people that have a birthday celebration on March 23rd...

[tt][blue]
Select *
From dbo.code
Where Month(birthd) = 3
And Day(birthd) = 23
[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

That can be done in one statement using the SUBSTRING method

update dbo.core
set birthyr = substring(birthd, 1, 4), 
    birthmn = substring(birthd, 6, 2), 
    birthdy = substring(birthd, 9, 2)
where id = '0000000'