Solved

Converting Date String

Posted on 2006-11-26
19
326 Views
Last Modified: 2012-06-27
Hello Experts,

I need to convert an nvarchar(50) field that has data formatted like Sunday, November 26, 2006 00:00:00:000 AM to a valid date format like 11/26/2006.

Thanks,

JackW
0
Comment
Question by:JackW9653
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18017581
declare @Date nvarchar(50)
SET @Date = 'Sunday, November 26, 2006 00:00:00:000 AM'
SELECT @date = RIGHT(@date,Len(@Date)-CharINDEX(',',@date))
SELECT CAST(@Date as datetime)
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 18017585
try this

select cast(right(@date,len(@date)-charindex(',',@date)) as datetime)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18017589
SELECT CAST( RIGHT(urDateColumn,Len(urDateColumn)-CharINDEX(',',urDateColumn)-1 ) as Datetime)
FROMurTable
0
 

Author Comment

by:JackW9653
ID: 18017640
Thanks for the quick replies but got a "Conversion Failed when converting datetime from character string" error.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 18017664
select cast(left(right(@date,len(@date)-charindex(',',@date)),3) as datetime)
0
 

Author Comment

by:JackW9653
ID: 18017678
same error Huy
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 18017683
sorry

select cast(left(right(@date,len(@date)-charindex(',',@date)),len(@date)-3) as datetime)
0
 

Author Comment

by:JackW9653
ID: 18017714
Huy,

This is what I am running, the field I am trying to convert is called DOB.

SELECT CAST(LEFT(RIGHT(DOB,Len(DOB)-CharINDEX(',',DOB)),Len(DOB)-3) AS DATETIME FROM tmpPatientData

still getting the same conversion failed error.

Thanks for your help,

Jack
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 18017731
If all data format like Sunday, November 26, 2006 00:00:00:000 AM, try to use rtrim and ltrim

SELECT CAST(LEFT(RIGHT(RTRIM(LTRIM(DOB)),Len(RTRIM(LTRIM(DOB)))-CharINDEX(',',RTRIM(LTRIM(DOB)))),Len(RTRIM(LTRIM(DOB)))-3) AS DATETIME FROM tmpPatientData
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18017763
Run this and paste the output

SELECT  RIGHT(DOB,Len(DOB)-CharINDEX(',',DOB)-1 ) as Datetime
tmpPatientData
0
 

Author Comment

by:JackW9653
ID: 18017775
December 16, 1988 00:00:00:0000 AM
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 18017780
just do
SELECT CAST(DOB AS DATETIME) FROM tmpPatientData
0
 
LVL 9

Expert Comment

by:dduser
ID: 18017805
Try this...

declare @Date nvarchar(50)
SET @Date = 'Sunday, November 26, 2006 00:00:00:000 AM'
SELECT @date = RIGHT(@date,Len(@Date)-CharINDEX(',',@date))
Select Convert(varchar(10),CAST(@Date as datetime),101)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18017807
JackW9653,
> December 16, 1988 00:00:00:0000 AM

In the above the time part Contains  4 zeros in the milli seconds part which is incorrect .. .u can write an update statement to replace that 4 zeros with 3

UPDATE tmpPatientData
SET DOB = REPLACE(dob, '0000','000')

then use the above update statement

0
 

Author Comment

by:JackW9653
ID: 18017816
Sorry Huy that didn't work - conversion failure again - it will probably work after the conversion aneesh proposes

aneesh - is there a way to run your query and convert the data in the DOB field directly?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18017822
JackW9653,
> aneesh - is there a way to run your query and convert the data in the  DOB field directly?

are u storing any time portion out there or is it '00:00:000 AM' for all the records .... also check my  last post
0
 

Author Comment

by:JackW9653
ID: 18017828
we were cross posting sorry - but no time portion is needed
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18017856
SELECT CAST(SUBSTRING(DOB, CHARINDEX( ',',DOB)+2, CHARINDEX( '00:',DOB) -CHARINDEX( ',',DOB)-3) as datetime)DOB
FROM tmpPatientData
0
 

Author Comment

by:JackW9653
ID: 18017993
Excellent aneesh - I changed your last select into an UPDATE and it worked, I now have Nov 26 2006 12:00AM. Which I was able to convert to a true DateTime.

Thanks for sticking with this one and thanks to Huy also for contributing.

Jack
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

914 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

19 Experts available now in Live!

Get 1:1 Help Now