Post reply

Inserting a varchar to a date

  • 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

  • May 3, 2005 at 7:57 am

    #556527

    Try converting the dateformat before your SQL statement ie: -

    SET DATEFORMAT ymd

    SQL Statement

  • May 3, 2005 at 7:58 am

    #556530

    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

  • May 3, 2005 at 8:07 am

    #556534

    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 ?

  • May 3, 2005 at 8:41 am

    #556540

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

  • May 3, 2005 at 9:07 am

    #556550

    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 ?

  • May 3, 2005 at 9:12 am

    #556554

    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.

  • May 4, 2005 at 4:03 am

    #556707

    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]

  • May 4, 2005 at 6:18 am

    #556727

    I suck with that convert function...

  • May 4, 2005 at 9:02 am

    #556794

    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))))

  • May 5, 2005 at 1:02 am

    #556994

    Thanks guys this worked nice.

    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