none
how to convert date in (varchar(50) format) into date in (datetime(8)format)

    Question

  • i have a table with 3 fields

    name(varchar(50)) , olddate(varchar(50)) , newdate(datetime(8))

    midhu                         07/04/2012                     <null>

    milan                           07/04/2012                     <null>

    milu                            07/04/2012                     <null>

    viidhcha                      07/04/2012                     <null>

    zhidhu                         07/04/2012                     <null>

    i want to transfer olddate values to newdate field

    my problem is one as varchar and other as datetime datatype

    Monday, March 25, 2013 10:50 AM

Answers

  • I would like the convert instead of cast becaus eI can add a style:

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL Insert into T1 Select 'midhu','12/04/2012',NULL       
    Insert into T1 Select 'midhu','12/13/2012',NULL       
    Insert into T1 Select 'midhu','13/12/2012',NULL       
    Update T1 Set new= convert(datetime,Old ,101)
    Select * From T1
    Drop table T1

    Styles:
    101 USA  mm/tt/jjjj
    103 Britisch/Französisch tt/mm/jjjj

    • Marked as answer by midhous Tuesday, March 26, 2013 8:20 AM
    Monday, March 25, 2013 1:30 PM
  • @ Latheesh NK

    the field olddate has same date

    DD/MM/YYYY

    Try the below:

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL        
    Insert into T1 Select 'midhu','12/04/2012',NULL        
    Insert into T1 Select 'midhu','12/11/2012',NULL        
    Insert into T1 Select 'midhu','13/12/2012',NULL        
    Update T1 Set new= 
     SUBSTRING(Old,4,2)+ '/'+SUBSTRING(Old,0,3)+ '/'+SUBSTRING(Old,7,4)  From T1 
    Select * From T1
    Drop table T1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by midhous Tuesday, March 26, 2013 8:20 AM
    Tuesday, March 26, 2013 6:35 AM

All replies

  • You can try using CAST or CONVERT functions.

    update <tableName> set newdate= CAST (olddate as datetime)


    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Monday, March 25, 2013 11:02 AM
  • Are you looking for the below:

    Create Table T1(name varchar(50),Old varchar(50), new datetime) Insert into T1 Select 'midhu','07/04/2012',NULL Insert into T1 Select 'midhu','12/04/2012',NULL Insert into T1 Select 'midhu','12/13/2012',NULL Update T1 Set new= Old Select * From T1 Drop table T1



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, March 25, 2013 11:07 AM
  • error:

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    The statement has been terminated

    what i do?

    Monday, March 25, 2013 11:18 AM
  • Ok, Please let us know       '12/13/2012'  --- 12 represents Month ro date?

    If 12 represents month, then there is some data issue in your table. You may try the below to avoid the error.

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL        
    Insert into T1 Select 'midhu','12/04/2012',NULL        
    Insert into T1 Select 'midhu','12/13/2012',NULL        
    Insert into T1 Select 'midhu','13/12/2012',NULL        
    Update T1 Set new= cast(Old as datetime) where ISDATE(Old)=1
    Select * From T1
    Drop table T1

    Suppose, the 12 represents a date, Then use substring function to split the old value and insert into new column.

    Create Table T1(name varchar(50),Old varchar(50), new datetime) Insert into T1 Select 'midhu','07/04/2012',NULL Insert into T1 Select 'midhu','12/04/2012',NULL Insert into T1 Select 'midhu','12/11/2012',NULL Insert into T1 Select 'midhu','13/12/2012',NULL Update T1 Set new= SUBSTRING(Old,4,2)+ '/'+SUBSTRING(Old,0,3)+ '/'+SUBSTRING(Old,7,4) From T1 Select * From T1 Drop table T1



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, March 25, 2013 11:29 AM
  • Check any value of OLDdate column is in out of range of Datetime data type datetime

    If yes try using datetime2 datetime2


    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Monday, March 25, 2013 11:31 AM
  • I would like the convert instead of cast becaus eI can add a style:

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL Insert into T1 Select 'midhu','12/04/2012',NULL       
    Insert into T1 Select 'midhu','12/13/2012',NULL       
    Insert into T1 Select 'midhu','13/12/2012',NULL       
    Update T1 Set new= convert(datetime,Old ,101)
    Select * From T1
    Drop table T1

    Styles:
    101 USA  mm/tt/jjjj
    103 Britisch/Französisch tt/mm/jjjj

    • Marked as answer by midhous Tuesday, March 26, 2013 8:20 AM
    Monday, March 25, 2013 1:30 PM
  • It's a simple query to convert your date field into datetime.

    UPDATE 'TABLE'

    SET NEW = CONVERT(DATETIME,OLD)

    Monday, March 25, 2013 1:40 PM
  • Incorrect.  Style can be used when converting strings to date-based datatypes.  Example:

    declare @test varchar(10);
    set @test = '12/10/13';

    select convert(date, @test, 1) as 'mm/dd/yy', convert(date, @test, 3) as 'dd/mm/yy';

    Monday, March 25, 2013 2:37 PM
  • I would like the convert instead of cast becaus eI can add a style:

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL Insert into T1 Select 'midhu','12/04/2012',NULL       
    Insert into T1 Select 'midhu','12/13/2012',NULL       
    Insert into T1 Select 'midhu','13/12/2012',NULL       
    Update T1 Set new= convert(datetime,Old ,101)
    Select * From T1
    Drop table T1

    Styles:
    101 USA  mm/tt/jjjj
    103 Britisch/Französisch tt/mm/jjjj


    Deleted - shown to be incorrect

    Chuck Pedretti | Magenic – North Region | magenic.com



    ?????
    Monday, March 25, 2013 2:44 PM
  • I would like the convert instead of cast becaus eI can add a style:

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL Insert into T1 Select 'midhu','12/04/2012',NULL       
    Insert into T1 Select 'midhu','12/13/2012',NULL       
    Insert into T1 Select 'midhu','13/12/2012',NULL       
    Update T1 Set new= convert(datetime,Old ,101)
    Select * From T1
    Drop table T1

    Styles:
    101 USA  mm/tt/jjjj
    103 Britisch/Französisch tt/mm/jjjj


    Deleted - shown to be incorrect

    Chuck Pedretti | Magenic – North Region | magenic.com



    ?????

    Scott Morris pointed out that my answer was in error.  Should have just deleted the post entirely to avoid confusion


    Chuck Pedretti | Magenic – North Region | magenic.com

    Monday, March 25, 2013 2:46 PM
  • Ah ok,

     I thought you mean that my answer is incorrect

    Monday, March 25, 2013 3:20 PM
  • @ Latheesh NK

    the field olddate has same date

    DD/MM/YYYY

    Tuesday, March 26, 2013 6:23 AM
  • @CHRISTA Kurschat

    thanks ...........

    but now a little problem ...

    becouse now the date like

    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00

    how to disable this  0:00:00

    Tuesday, March 26, 2013 6:34 AM
  • @ Latheesh NK

    the field olddate has same date

    DD/MM/YYYY

    Try the below:

    Create Table T1(name varchar(50),Old varchar(50), new datetime)
    Insert into T1 Select 'midhu','07/04/2012',NULL        
    Insert into T1 Select 'midhu','12/04/2012',NULL        
    Insert into T1 Select 'midhu','12/11/2012',NULL        
    Insert into T1 Select 'midhu','13/12/2012',NULL        
    Update T1 Set new= 
     SUBSTRING(Old,4,2)+ '/'+SUBSTRING(Old,0,3)+ '/'+SUBSTRING(Old,7,4)  From T1 
    Select * From T1
    Drop table T1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by midhous Tuesday, March 26, 2013 8:20 AM
    Tuesday, March 26, 2013 6:35 AM
  • @ Latheesh NK

    thanks

    Update T1 Set new= 
     SUBSTRING(Old,4,2)+ '/'+SUBSTRING(Old,0,3)+ '/'+SUBSTRING(Old,7,4)  From T1 
    Select * From T1

    its also working ,but with same problem

    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00
    23/03/2013 0:00:00

    how to disable this  0:00:00

    Tuesday, March 26, 2013 6:59 AM
  • Ok, Change the column datatype from datetime to date. This will help you.

    However, you should not worry about the time in datetime. you can format it while showing it to the consuming application.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Tuesday, March 26, 2013 7:05 AM
  • @ Latheesh NK

    but in this there is no date datatype 

    only date time in visual studio 2005

    Tuesday, March 26, 2013 7:45 AM
  • It's better to tell us which is your sql server version :-)

    You can't cut the time part if you use datetime. It's a fix part of taht datatype.

    Tuesday, March 26, 2013 7:59 AM
  • The datetime datatype always has a time part as well. So you cannot avoid this. What you can do is to format the date value in the calling application (The date data type is only available from SQL Server 2008 and later versions that only stores the date part)


    Krishnakumar S

    Tuesday, March 26, 2013 8:00 AM
  • sqlserver 2005, its in visual studio2005

    my task is read a text data in notepad  (23/03/2013) , i used varchar for reading because datatype datetime can't read text file date like (23/03/2013) ,then this data transfer into another table ...........

    but this table field in datetime and hide time values for another comparison

    my task is to insert date in datetime field... only date

     
    Tuesday, March 26, 2013 8:11 AM
  • @ Christa Kurschat and @ Latheesh NK

    sorry................

    in database , there is only date.............

    thanks ......

    thanks to all

    Tuesday, March 26, 2013 8:19 AM
  • @CHRISTA Kurschat

    how to convert one table to other

    am using

    "Update temp Set temp.date= convert(datetime,Table1.dat ,103) Select Table1.dat From Table1"

    but error:The column prefix 'Table1' does not match with a table name or alias name used in the query.

    Tuesday, March 26, 2013 10:18 AM
  • hi midhous, please follow the other link....

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1048f3c3-ab29-4c21-955a-2a1e39659c56


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, March 26, 2013 10:20 AM