update statement to change dates into yes/no

ac_davis2002
ac_davis2002 used Ask the Experts™
on
hi

I have a database table called contracts, I have a datetime field in the table called renew, the field contains datetime values. what i would like to do is up date the values to yes or no. basilcally if the renew date is in the past set the value to yes if the date is in the future set to no.

firstly do i need to create a new column

secondly can anyone please give me the code to do the update please.?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
you need a computed column
ALTER TABLE urTable
 ADD newColumn  AS (CASE WHEN dt < GETDATE() THEN 'Yes' ELSE 'NO' END )
Top Expert 2010
Commented:
ac_davis2002 said:
>>firstly do i need to create a new column

Yes, because you cannot store text in a datetime column

>>secondly can anyone please give me the code to do the update please.?

I urge you not to do this.  Or at least, if you do, to use a calculated column.
Top Expert 2010
Commented:
matthewspatrick said:
>>I urge you not to do this.  Or at least, if you do, to use a calculated column.

And I see now that aneesh was way ahead of me, as usual :)
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
thanks guys!

one last point, the table in question is just a dataload table I use to import data from excel. If I change the renew column to be a varchar could I use a statement to update the values to yes no? could anyone give me an example of the statement to up date the values to yes or no if the column was a varchar type?
AneeshDatabase Consultant
Top Expert 2009
Commented:
if you are using a computed column as i shown, you dont really need an updated statement

Author

Commented:
so to be dum but could you give me of an example statement anyway to update the date...sorry to be a pain!

Author

Commented:
for example if the column was varchar column that held dates 01/12/2009, 01/12/2010 etc how could I update to yes no if the date is in the past or in the future..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial