Inserting a varchar to a date

Question

Post reply

Inserting a varchar to a date

Douwe

SSC Enthusiast

Points: 101
More actions
May 3, 2005 at 7:53 am

#163386

Hi,
I have a varchar(8) value in a table that has a common primary key to the tabel I want to update.
The varchar values is dates in the format '00/08/12' saying 12 August 2000. Now I use a convert function:
"select convert(datetime, REG_DATE) from temp"
but this always gives me a "Arthimentric Overflow... " error. I then removed all inconsist data: "DELETE FROM TEMP WHERE NOT REG_DATE LIKE '%/%/%'" and checked for nulls etc. still...
Now I have two questions:
How can I change the data format so that it can conform to the convert (or is there some better convert function).
How can I update multi fields in the other table to where I want to update this.
"Insert into table ... set X= SElect ... ???
Please help
Regards
Douwe Bijkersma

Viewing 11 posts - 1 through 10 (of 10 total)

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

Rafter SSC-Addicted Points: 472 More actions · Answer 1

Try converting the dateformat before your SQL statement ie: -

SET DATEFORMAT ymd

SQL Statement

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

There probabely is a better way to do it but have you considered appending 19 or 20 to the begenning of the field so that sql server can have an easier time determining what part of the field is the year?

Because this works for me :

Select convert(datetime, '2000/08/12')

--2000-08-12 00:00:00.000

Douwe SSC Enthusiast Points: 101 More actions · Answer 3

Sure I'll try that, but how would I make an update checking the first few chars and so determining if it should be 20 or 19 and then putting it at the begining ? In SQL ? or must I create my own little program for it ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Select case left(colname, 1) WHEN '0' THEN '20' ELSE '19' END + Colname as NewDateCol.

Douwe SSC Enthusiast Points: 101 More actions · Answer 5

I inserted the 19 and 20 prefixes but now I still got the problem.. By using the top I figured that there was a currupt value that had the date "2000/10/0". This gave the error..

Now how can I update the table by using primary keys ?

I want to use

Update TABLE SET REGDATE = SELECT REG_DATE from temp where tabel.id = temp.id

HOW CAN I DO THIS ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Update Perm set Perm.RegDate = Temp.RegDate from dbo.PermTable Perm inner join tempTable Temp on Perm.id = Temp.id

Make sure you have a backup of the table before you do the update... or even better try the update on a non production table.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Remi, did you try

set dateformat ymd

select

convert(datetime, '00/08/12')

, convert(datetime, '99/08/12')

, convert(datetime, '50/08/12')

, convert(datetime, '49/08/12')

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

May 4, 2005 at 6:18 am

#556727

I suck with that convert function...

David Thayer SSChasing Mays Points: 612 More actions · Answer 9

There are styles for the convert function that show the date for different formats:

Otherwise, you could adapt this (which I use to convert 120101 to time 12:01:01)

(convert(datetime,(Left(dbo.tblTemp.ScanTime,2)+ ':' + Substring(dbo.tblTemp.ScanTime,3,2) + ':' + Substring(dbo.tblTemp.ScanTime,5,2))))

Douwe SSC Enthusiast Points: 101 More actions · Answer 10

Thanks guys this worked nice.

Regards

Douwe Bijkersma