Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Converting Date String

Posted on 2006-11-26
19
Medium Priority
?
372 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

824 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