Need help with UPDATE and SET commands

Question

Post reply

Need help with UPDATE and SET commands

bkana

Valued Member

Points: 67
More actions
May 1, 2009 at 6:23 am

#202826

Hello ,
I have to remove certain data from fields in a column called "ExpirationDate" I need to "empty out" the fields that have '12/31/2008' in the field.
There are other dates in the same column that I need to retain, as well as the other rows in the table - I just need to delete anything with a specific date of '12/31/2008' from the ExpirationDate column (while keeping everything else in the row). Hope that made sense. I think I can use the following, but am not sure. I have tried
UPDATE Contacts
SET ExpirationDate = 'NULL'
WHERE ExpirationDate = '12/31/2008'
AND:
UPDATE Contacttest
SET CFDExpirationDate = 'NULL'
WHERE (CONVERT(varchar(11), CFDExpirationDate, 1) = '12/31/2008')
but this thows a "Syntax error converting datetime from character string" and I know it's because the date fields I am trying to change are more like ""12/31/2008 12:11:05.5445 PM"
What am I missing?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

See if the below works to get all the dates that you are looking for.

select * from ContactsTest where substring(cast(ExpirationDate as varchar),1,11) = 'Dec 31 2008'

If yes then, you can run the UPDATE statement for one row(To be safe and hopefully have the backup before u do it), and then rest of the rows

UPDATE ContactTest SET ExpirationDate = NULL WHERE substring(cast(ExpirationDate as varchar),1,11) = 'Dec 31 2008'

bkana Valued Member Points: 67 More actions · Answer 2

Thanks Mayank,

That worked on my test view, going to run a backup and run it against my working view. While I run the backup could you explain a little more the query you gave me and sort of break down the parts - more importantly, the

WHERE substring(cast(CFDExpirationDate as varchar),1,11) = 'Dec 31 2008'

I assume by using 'Dec 31 2008' you were telling it to use every day in 2008 (or before Dec 31 2008).

I thought I had to somehow pass a convert date command before using the SET command - was I way off?

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Mayank Khatri (5/1/2009)
See if the below works to get all the dates that you are looking for.
select * from ContactsTest where substring(cast(ExpirationDate as varchar),1,11) = 'Dec 31 2008'
If yes then, you can run the UPDATE statement for one row(To be safe and hopefully have the backup before u do it), and then rest of the rows
UPDATE ContactTest SET ExpirationDate = NULL WHERE substring(cast(ExpirationDate as varchar),1,11) = 'Dec 31 2008'

You could also use:

WHERE CONVERT(varchar(8), ExpirationDate, 112) = '20081231'

This would avoid the substring operation and probably improve performance.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 4

bkana (5/1/2009)
Thanks Mayank,
That worked on my test view, going to run a backup and run it against my working view. While I run the backup could you explain a little more the query you gave me and sort of break down the parts - more importantly, the
WHERE substring(cast(CFDExpirationDate as varchar),1,11) = 'Dec 31 2008'
I assume by using 'Dec 31 2008' you were telling it to use every day in 2008 (or before Dec 31 2008).
I thought I had to somehow pass a convert date command before using the SET command - was I way off?

I have used the equal sign, so for all the rows on DEC 31 2008, CFDExpirationDate should be NULL. For before I would have used 'Dec 31 2008' .

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 5

bkana (5/1/2009)
Hello ,
I have to remove certain data from fields in a column called "ExpirationDate" I need to "empty out" the fields that have '12/31/2008' in the field.
There are other dates in the same column that I need to retain, as well as the other rows in the table - I just need to delete anything with a specific date of '12/31/2008' from the ExpirationDate column (while keeping everything else in the row). Hope that made sense. I think I can use the following, but am not sure. I have tried
UPDATE Contacts
SET ExpirationDate = 'NULL'
WHERE ExpirationDate = '12/31/2008'
AND:
UPDATE Contacttest
SET CFDExpirationDate = 'NULL'
WHERE (CONVERT(varchar(11), CFDExpirationDate, 1) = '12/31/2008')
but this thows a "Syntax error converting datetime from character string" and I know it's because the date fields I am trying to change are more like ""12/31/2008 12:11:05.5445 PM"
What am I missing?

The syntax error was because your ExpiratioDate column is datetime column whereas '12/31/2008' is Varchar column