Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert field and add it to table.

Posted on 2011-02-21
8
Medium Priority
?
375 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:K V
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

618 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