update a date field - SQL Server Forums

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

update a date field

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2008-07-18 : 05:59:05

I know how to update numeric and text fields in my database, but have no idea on how to 'blank off' a date field. I try this and it doesnt like it

update wce_contact
set mail1date = ''
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')

any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 06:06:28

if its a nullable field, set it to null

Topaz
Posting Yak Master

199 Posts

Posted - 2008-07-18 : 06:13:19

its not nullable or at least i dont think so. how would i check that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 06:29:10

quote:
Originally posted by Topaz

its not nullable or at least i dont think so. how would i check that?

SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=yourcolumnname

if 1 then its nullable.

Topaz
Posting Yak Master

199 Posts

Posted - 2008-07-18 : 06:33:27

I had to use:

SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'mail1date'

and the results say 40 rows with column IS_NULLABLE all with 'YES' written in them. not sure what that means...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 06:44:47

quote:
Originally posted by Topaz

I had to use:

SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'mail1date'

and the results say 40 rows with column IS_NULLABLE all with 'YES' written in them. not sure what that means...

that menas you have 40 columns with this name in various tables and all of them are of nullable type.see value of TABLE_NAME to determine the tables

SELECT TABLE_NAMEIS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'mail1date'

Topaz
Posting Yak Master

199 Posts

Posted - 2008-07-18 : 07:08:01

ok just tried nulling the date filed and got this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 07:45:44

show us the syntax you used to try and null it?

Em

Topaz
Posting Yak Master

199 Posts

Posted - 2008-07-18 : 07:55:57

update wce_contact
set mail1date = 'null'
where expressemail in ('example@example.com')

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-18 : 08:00:46

= NULL

Em