Update column based on DATE BETWEEN

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 2008 Forums

Transact-SQL (2008)

Update column based on DATE BETWEEN

Author

Topic

RayanJ
Starting Member

1 Post

Posted - 2012-10-27 : 11:16:27

Hi Guys,

Can someone help me with a query on SQL server 2008?

I have a table called Persons, containing fields
'PersonID', 'Birthdate', 'StarSign'
The star sign field is currently NULL

Another table is called Astrology:
'BirthdateFrom', 'BirthdateTo', 'StarSign'

How can I update Persons.Starsign to Astrology.StarSign based on when each persons birthdate is?

Thanks All, Would really appreciate some help!

Rayan
London

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-27 : 12:00:47

update Persons
set starsign = a.starsign
from Persons p
join Astrology a
on p.birthdate between a.birthdatefrom and a.birthdateto

That's assuming none of these dates include a year.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-27 : 21:22:59

does all date fields have time part? if BirthdateFrom,BirthdateTo have no timepart you might have to use this instead for date logic


...
and p.birthdate >=a.birthdatefrom 
and p.birthdate < a.birthdateto+1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources