Solved

convert varchar to datetime

Posted on 2009-05-18
3
579 Views
Last Modified: 2012-05-07
I have the following table
Create table zzNUoSNMIDateRange
(
NMI varchar(10)
, FromDate date
, ToDate date
, Quantity Float
, FRMP varchar(10)
)

and I need to transfer data from another table to this one. Now all the data in the second table is is of the data type VARCHAR(50)

I need to copy this data :
INSERT INTO zzNUoSNMIDateRange
(NMI, FromDate, ToDate)
Select distinct [Column 7],
CAST ( [Column 11] AS datetime),
CAST ( [Column 12] AS datetime)
 from zzExtractNUOS


also data in  [Column 11]  and  [Column 12] is in this format ' 20080826'

 i get this error when i try the conversion,
"Conversion failed when converting date and/or time from character string."
0
Comment
Question by:manivineet
3 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 200 total points
ID: 24419195
Hi,

There could be numerous reasons to why, so start by showing the offending dates.

SELECT [Column 11] FROM zzExtractNUOS WHERE ISDATE([Column 11])=0
SELECT [Column 12] FROM zzExtractNUOS WHERE ISDATE([Column 12])=0

/peter
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 24419257
please try this code. it that does also fail, you have indeed bad data.
INSERT INTO zzNUoSNMIDateRange
(NMI, FromDate, ToDate)
Select distinct [Column 7], 
CONVERT(datetime, [Column 11] , 112),
CONVERT(datetime, [Column 12] , 112)
 from zzExtractNUOS

Open in new window

0
 
LVL 11

Assisted Solution

by:Muhammad Ousama Ghazali
Muhammad Ousama Ghazali earned 150 total points
ID: 24419271
Try using the following:
INSERT INTO zzNUoSNMIDateRange
(NMI, FromDate, ToDate)
Select DISTINCT [Column 7], 
CONVERT(DATETIME, SUBSTRING(Column 11, 1, 4) + '-' + SUBSTRING(Column 11, 5, 2) + '-' + SUBSTRING(Column 11, 7, 2)),
CONVERT(DATETIME, SUBSTRING(Column 12, 1, 4) + '-' + SUBSTRING(Column 12, 5, 2) + '-' + SUBSTRING(Column 12, 7, 2))
FROM zzExtractNUOS

Open in new window

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now