[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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
0
Biju708
Asked:
Biju708
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
deightonCommented:
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now