Solved

Converting Date String

Posted on 2006-11-26
19
357 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
[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
  • 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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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