String date to datatype datetime

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

Transact-SQL (2000)

String date to datatype datetime

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-13 : 10:30:28

Hi i have a feild in my .txt file that shows the date as
"12-Mar-2007" so i set the column in the table to varchar and then i want to update my table and create a new column called new_date, based on the old date format "12-Mar-2007", but i want my new date format to be of datetime datatype. So i used this code

UPDATE tbl_Temp_out
SET NEW_CREATED_DT = CONVERT(varchar, CREATED_DT, 101)

But am getting a error on this Syntax error converting datetime from character string.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 10:33:20

Create the new column NEW_CREATED_DT as datetime


UPDATE tbl_Temp_out
SET NEW_CREATED_DT = CONVERT(varchar datetime, CREATED_DT, 101)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-13 : 10:34:29

1. Don't use varchar to store dates
2. Do date formatting on the front-end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-13 : 10:55:44

Sorry i made a mistake in the code i had it the way you give it to me
CONVERT(datetime, CREATED_DT, 101) i used the varchar as a work around.. but am still getting that error

With the code below..

My CREATED_DT column is data type varchar and i want my new colum NEW_CREATED_DT to be dada type datetime

UPDATE tbl_Temp_out
SET NEW_CREATED_DT = CONVERT(datetime, CREATED_DT, 101)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-13 : 11:02:07

Make sure you have valid and consistent date format for all the values of CREATED_DT column using ISDATE() function.

Select Created_DT
from tbl_temp
where isdate(Created_DT) = 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-13 : 11:10:43

Here are the results am getting form that query they are not all consistent as you can see,

I did a distinct query on the column and am getting back these numbers in the cloumn Created_DT, so this is were my probem is..

66.64
58.15
84.20
5.88
16.08
14.50
14.50
14.50
24.93
23.99

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-13 : 11:12:51

Are these dates at all?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-03-13 : 11:21:54

My extract has 190,000 rows in it and they are not dates, they look like the column before the date column in the extract, some how they are getting pulled into the wrong column am getting a error on the import it reads as follows "Too many columns found in the current row; non-whitespace characters were found after the last defined column's data. " the error is happening in rown 150,000 but i can see any difference in the row compared to the other rows..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources