Solved

Convert field and add it to table.

Posted on 2011-02-21
8
373 Views
Last Modified: 2012-05-11
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
Comment
Question by:Biju708
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 34941209
Should the new field be a varchar field or  datetime?

I suggest using a datetime one. It will spare you many future issues.
0
 

Author Comment

by:Biju708
ID: 34941257
it will be datetime,

thanks Kobe
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 34941323

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
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 34941544
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
 

Author Comment

by:Biju708
ID: 34941652
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
 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 34941735
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
 
LVL 8

Expert Comment

by:rushShah
ID: 34941819

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
 

Author Closing Comment

by:Biju708
ID: 34941825
Perfect !!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question