Convert field and add it to table.

Hi all,
    I have an SQL table with a coloumn that has text entered in the following format 20051126173918 I need to convert that into andenter it in new coloumn created as a Date 2005/11/26 17:39:18 what do you suggest?. Below is how the table should look.

regards

ID                 TEXT              Date                                    NEWDATE
3                  some text      20051126173918                2005/11/26 17:39:18
6                  moretext        20051226173914                2005/12/26 17:39:14
Biju708Asked:
Who is Participating?
 
deightonConnect With a Mentor Commented:
UPDATE aDATE SET NEWDATE = CAST(SUBSTRING(DATE,1,4) + '-' + SUBSTRING(DATE,5,2) + '-' + SUBSTRING(DATE,7,2)  + ' ' + SUBSTRING(DATE,9,2) + ':' + SUBSTRING(DATE,11,2) + ':' + SUBSTRING(DATE,13,2)AS DATETIME)

Open in new window

0
 
Kobe_LenjouCommented:
Should the new field be a varchar field or  datetime?

I suggest using a datetime one. It will spare you many future issues.
0
 
Biju708Author Commented:
it will be datetime,

thanks Kobe
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
K VDatabase ConsultantCommented:

ALTER TABLE TABLENAME ADD COLUMN `newdate` DATETIME NOT NULL AFTER `date`;
update TABLENAME set newdate=str_to_date(date,'%Y%m%d%k%i%s') ;
0
 
rajeevnandanmishraCommented:
Hi Biju,

I am not aware of the str_to_date function in SQL.
But below code is tested to be run in SQL as per your condition. Give it a try:
 
ALTER TABLE myTableName ADD COLUMN newDate DATETIME NULL

UPDATE myTableName 
SET newDate = convert(datetime, left([Date],8), 112)  + convert(datetime,substring[Date],9,2) + ':' 
              + substring[Date],3,2) + ':' + right[Date],2), 108)

Open in new window


rnm
0
 
Biju708Author Commented:
Thanks rajee I have a syntax error though where I marked in bold

UPDATE [TABLE023].[dbo].[PORTAL_MEMBERS]
SET newDate = convert(datetime, left([M_DATE],8), 112)  + convert(datetime,substring[M_DATE],9,2)+ ':' + substring[M_DATE],3,2) + ':' + right[M_DATE],2), 108)

It's giving me invalid coloumn name Substring
0
 
rushShahCommented:

UPDATE TableName
SET newDate = convert(VARCHAR(15), convert(datetime, left([DATE],8)), 111)  + ' ' +
 substring([DATE],9,2)+ ':' + substring([DATE],11,2) + ':' + right([DATE],2)

Open in new window

0
 
Biju708Author Commented:
Perfect !!
0
All Courses

From novice to tech pro — start learning today.