DateTime question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

DateTime question

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2004-06-15 : 16:37:27

Two quick questions.

Question 1.

I have a column called "date_completed".
It is currently a "varchar(20)".
I want to change it to be "datetime".

When I attempt to do that I get the error:

quote:
'DRG_Inquiries' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

The values in that column or either null or look like this:


1/8/2002
1/17/2002
1/2/2002
1/17/2002
1/8/2002
1/3/2002
1/3/2002
1/2/2002
1/4/2002
1/17/2002

So I'm not sure why it can't convert it.

Question 2:

2. Since I can't seem to force SQL to make my life easier, I created a new column called "Completed" and made it a "datetime" datatype. I want to capy all the values from "date_completed" into "completed" but I'm trying to do it without a cursor.

I tried this:


update drg_inquiries 
set completed_date=b.date_completed
from drg_inquiries a, drg_inquiries b
where b.date_Completed is not null
and a.uid=b.uid

But it gives me a

quote:
Server: Msg 8154, Level 16, State 1, Line 1
The table 'drg_inquiries' is ambiguous.

which I suppose makes perfect sense.

So, any thoughts?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-15 : 16:42:40

You Will have to use a cast or convert.

Example :

Declare @datem Varchar(20)

Select @datem = '1/8/2002'

Select CAst(@Datem As Datetime)

Returns 2002-01-08 00:00:00.000

Yours

update drg_inquiries
set Cast(completed_date as datetime)=b.date_completed
from drg_inquiries a, drg_inquiries b
where b.date_Completed is not null
and a.uid=b.uid

Jim
Users <> Logic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-15 : 16:49:16

wait wait...

you are joining the table to itself for no apparent reason.

all you should have to do is:

update drg_inquiries
Set completed = cast(completed_date as datetime)
From drg_inquiries
Where date_Completed is not null
/*********************
maybe add the following too
*********************/
and isDate(date_Completed)=1

Corey

label
Posting Yak Master

197 Posts

Posted - 2004-06-15 : 17:05:42

quote:
Originally posted by Seventhnight

wait wait...

you are joining the table to itself for no apparent reason.

all you should have to do is:

update drg_inquiries
Set completed = cast(completed_date as datetime)
From drg_inquiries
Where date_Completed is not null
/*********************
maybe add the following too
*********************/
and isDate(date_Completed)=1

Corey

I thought about doing it that way but wasn't sure which value of 'date_completed' it would grab if I didn't use some sort of identity field to ensure valid data?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-15 : 22:17:56

if in doubt, try it out:

web.dbo.webTraffic 'VisitsPerDay'

Declare @myTable table (col1 nvarchar(20), col2 datetime)
Insert Into @myTable Select col1 = '4/23/1980', col2 = null
Insert Into @myTable Select col1 = '2/28/2004', col2 = null
Insert Into @myTable Select col1 = '2/29/2004', col2 = null
Insert Into @myTable Select col1 = '2/30/2004', col2 = null
Insert Into @myTable Select col1 = null, col2 = null

/*********************
fails
*********************/
Select * From @myTable
update @myTable
Set col2 = cast(col1 as datetime)
From @myTable
Where col1 is not null

Select * From @myTable
/*********************
need to add this condition
*********************/
update @myTable
Set col2 = cast(col1 as datetime)
From @myTable
Where col1 is not null
and isDate(col1)=1

Select * From @myTable

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources