Solved

Converting Date String

Posted on 2006-11-26
19
354 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

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 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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

737 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