Solved

Convert field and add it to table.

Posted on 2011-02-21
8
372 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Many to one in one row 2 40
Need some alteration to below mention query 2 13
Using datetime as triggers 2 26
SQL Server code help needed 14 35
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 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