Converting Date String

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
JackW9653Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT CAST(SUBSTRING(DOB, CHARINDEX( ',',DOB)+2, CHARINDEX( '00:',DOB) -CHARINDEX( ',',DOB)-3) as datetime)DOB
FROM tmpPatientData
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
HuyBDCommented:
try this

select cast(right(@date,len(@date)-charindex(',',@date)) as datetime)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CAST( RIGHT(urDateColumn,Len(urDateColumn)-CharINDEX(',',urDateColumn)-1 ) as Datetime)
FROMurTable
0
 
JackW9653Author Commented:
Thanks for the quick replies but got a "Conversion Failed when converting datetime from character string" error.
0
 
HuyBDCommented:
select cast(left(right(@date,len(@date)-charindex(',',@date)),3) as datetime)
0
 
JackW9653Author Commented:
same error Huy
0
 
HuyBDCommented:
sorry

select cast(left(right(@date,len(@date)-charindex(',',@date)),len(@date)-3) as datetime)
0
 
JackW9653Author Commented:
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
 
HuyBDCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Run this and paste the output

SELECT  RIGHT(DOB,Len(DOB)-CharINDEX(',',DOB)-1 ) as Datetime
tmpPatientData
0
 
JackW9653Author Commented:
December 16, 1988 00:00:00:0000 AM
0
 
HuyBDCommented:
just do
SELECT CAST(DOB AS DATETIME) FROM tmpPatientData
0
 
dduserCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
JackW9653Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
JackW9653Author Commented:
we were cross posting sorry - but no time portion is needed
0
 
JackW9653Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.