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
 Date conversion error

Author  Topic 

clinton_eg
Yak Posting Veteran

60 Posts
Posted - 2010-02-15 : 12:02:51
I have the following Query where i convert a text (dd-mm-yy) to date (yyyy-mm-dd 00:00:00.000) and transfer into a final result table.

/* t_date is a varchar(50) data type*/
update Temp
set Temp.t_date = convert(datetime,Temp.t_date,103)

/*convert to datetime data type*/
alter table ScopeDataTemp
alter column SVG_date datetime

/*update final table where destination column f_date is in datetime format*/
insert into Final
Select Temp.t_date from Temp.

However when i run the above Query i get the following error.

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

Please could you help and let me know what is wrong .. Thanks

Ewan Gilby

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-15 : 12:06:32
why are you using varchar field to store dates in temp? it should be actually datetime data type. Always use proper datatypes for your fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-15 : 12:08:43
convert(datetime,Temp.t_date,103)

assumes that the value in t_date is a string, and it is in the format dd/mm/yyyy

Also, converting it to DateTime and storing it back in the SAME (i.e. string/varchar) column accomplishes nothing I'm afraid (except telling you you've got a date that is invalid in that format, as per the error your got ).

If you have dates in varchar columns you need to convert them to datetime in a different column, or into a different table (temporary) table, and then change the datatype of the original column and copy them back.

I think you are trying to do something like that, but I'm not quite sure.

If you want to know if you have any varchar dates with invalid format then:

SET dateformat dmy -- Change the "dmy" sequence to match the format of your data

SELECT *
FROM TEMP
WHERE IsDate(t_date) = 0
      OR t_date NOT LIKE '[0-3][0-9]/[0-1][0-9]/[12][90][0-9][0-9]'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-15 : 12:14:19
quote:
Originally posted by Kristen


SELECT *
FROM TEMP
WHERE IsDate(t_date) = 0
[/code]

ISDATE() is not fully reliable so you might need to add additional conditions to make 100% sure that date is in format you wanted.
consider examples like
SELECT ISDATE('2008')
SELECT ISDATE('200809') etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

clinton_eg
Yak Posting Veteran

60 Posts
Posted - 2010-02-15 : 12:23:22
Hi Kristen,

the limitation is that the datasource is a .csv file which stores the date in a string type and format as dd/mm/yyyy.

quote:
Originally posted by Kristen

convert(datetime,Temp.t_date,103)

assumes that the value in t_date is a string, and it is in the format dd/mm/yyyy

Also, converting it to DateTime and storing it back in the SAME (i.e. string/varchar) column accomplishes nothing I'm afraid (except telling you you've got a date that is invalid in that format, as per the error your got ).

If you have dates in varchar columns you need to convert them to datetime in a different column, or into a different table (temporary) table, and then change the datatype of the original column and copy them back.

I think you are trying to do something like that, but I'm not quite sure.

If you want to know if you have any varchar dates with invalid format then:

SET dateformat dmy -- Change the "dmy" sequence to match the format of your data

SELECT *
FROM TEMP
WHERE IsDate(t_date) = 0



Ewan Gilby
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-15 : 12:23:54
"ISDATE() is not fully reliable so you might need to add additional conditions to make 100% sure that date is in format you wanted."

Done
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-15 : 12:25:06
quote:
Originally posted by Kristen

"ISDATE() is not fully reliable so you might need to add additional conditions to make 100% sure that date is in format you wanted."

Done

Cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-15 : 12:26:20
" the limitation is that the datasource us a .csv file which stores the date in a string type and format as dd/mm/yyyy."

Import to temporary table which has a "t_date" column as VARCHAR then convert directly into the actual table with:

INSERT INTO MyActualTable(Col1, ..., t_date, ...)
SELECT Col1,
       ....,
       CONVERT(datetime, SrcDate, 103),
       ....,
FROM TemporaryImportTable

(or using UPDATE if the target record already exists)
Go to Top of Page

clinton_eg
Yak Posting Veteran

60 Posts
Posted - 2010-02-15 : 12:26:20
Hi Visakh16
The source date is a Varchar data type . Hence tried the conversion part, but still facing issues.

the Temp.t_date shows as datetime and Final.f_date also is in datetime type, but still getting the mentioned error.

quote:
Originally posted by visakh16

why are you using varchar field to store dates in temp? it should be actually datetime data type. Always use proper datatypes for your fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Ewan Gilby
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-15 : 12:28:33
"update Temp
set Temp.t_date = convert(datetime,Temp.t_date,103)"

You are converting t_date and putting it back in the same t_date column

Assuming t_date is a VARCHAR date, then you need to store the DATETIME version into a different (i.e. DATETIME datatype) column - either an additional column in the same TEMP table, or directly into the final target column)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-15 : 12:32:28
quote:
Originally posted by clinton_eg

Hi Visakh16
The source date is a Varchar data type . Hence tried the conversion part, but still facing issues.

the Temp.t_date shows as datetime and Final.f_date also is in datetime type, but still getting the mentioned error.

quote:
Originally posted by visakh16

why are you using varchar field to store dates in temp? it should be actually datetime data type. Always use proper datatypes for your fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Ewan Gilby

Is the source format consistent or do you have mixed format dates present?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

clinton_eg
Yak Posting Veteran

60 Posts
Posted - 2010-02-16 : 05:43:53
Hi Kristen
I could use this, however if i want to check if data for that date already exists in the Final table before i actually update it, how do i do that?

If the data for that date exists, then i need to remove it from the Temp table and not transfer it to the Final Table.

quote:
Originally posted by Kristen

" the limitation is that the datasource us a .csv file which stores the date in a string type and format as dd/mm/yyyy."

Import to temporary table which has a "t_date" column as VARCHAR then convert directly into the actual table with:

INSERT INTO MyActualTable(Col1, ..., t_date, ...)
SELECT Col1,
       ....,
       CONVERT(datetime, SrcDate, 103),
       ....,
FROM TemporaryImportTable

(or using UPDATE if the target record already exists)


Ewan Gilby
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-16 : 05:56:20
This perhaps?

DELETE T
FROM MyTempTable AS T
    JOIN MyFinalTable AS F
        ON F.MyPK = T.MyPK
       AND F.MyDateTime = CONVERT(datetime, T.MyStringDate, 103)
Go to Top of Page

clinton_eg
Yak Posting Veteran

60 Posts
Posted - 2010-02-16 : 10:36:56
This is what i did:

i realised that when i do SELECT * INTO #temp FROM TEMP /*without converting the column Temp.t_date from Varchar(50) to datetime and after applying "update Temp set Temp.t_date = convert(datetime,Temp.t_date,103)"*/
the result in #temp shows as datetime.

So this is what i did

UPDATE Temp
SET Temp.t_date = CONVERT(DATETIME,Temp.t_date,103)

SELECT * INTO #temp FROM TEMP

DELETE FROM #temp WHERE #temp.t_date <= (SELECT MAX(Final.f_date) FROM Final)

INSERT INTO Final
Select #temp.t_date FROM Temp.

DROP TABLE #temp
DELETE FROM Temp

I want to thank all of your'll for the help . Really appreciate it. You all rock!!

Ewan Gilby
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-02-17 : 02:04:18
quote:
Originally posted by visakh16

quote:
Originally posted by Kristen

"ISDATE() is not fully reliable so you might need to add additional conditions to make 100% sure that date is in format you wanted."

Done

Cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Some people often forget the actual effects of ISDATE() and ISNUMERIC()

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-17 : 02:09:59
quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by Kristen

"ISDATE() is not fully reliable so you might need to add additional conditions to make 100% sure that date is in format you wanted."

Done

Cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Some people often forget the actual effects of ISDATE() and ISNUMERIC()

Madhivanan

Failing to plan is Planning to fail

Actually I also started to think about their usage seriously only recently, thanks to your well presented blogs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site