Date string to Date Format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date string to Date Format

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 15:53:31

I have been trying to look up a way to do this but I keep finding ways that still give me an error. I didn't create this database and don't understand why anyone stores date as a string but I have to work with it.

I have a date written like this '20080620' and I want it to be written like this 6/20/2008. I tried cast(b.appt_date as datetime) but get this error:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I have no time in the field its just a date so maybe thats the problem? If so is there another way around it?

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 15:59:36

It means you have an invalid date, or all strings are not formatted the same way.

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 16:08:16

Ok I am looking through the table and I see some values say NULL. Is that mostly likely the case? Is there a way to put in the select statement to only convert where the field is not null?

select a.first_name + ' ' + a.last_name as PtName,a.date_of_birth as PtDOB,c.enc_timestamp as DOV,
b.ref_initiated_date as DateProcessed,
b.appt_date,b.urgent_referral as Urgent,
b.ref_prov_first + ' ' + b.ref_prov_last as Primary_Provider,
b.referral_prov_first as Specialty_Provider,
b.specialty,
b.ref_initiatedby as ORW
from
person a
join chc_referral_form_ b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id

quote:
Originally posted by Peso

It means you have an invalid date, or all strings are not formatted the same way.

E 12°55'05.25"
N 56°04'39.16"

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:10:00

No, NULL is not the problem.
A string like "20080431" is.

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 16:19:25

I don't understand because I have done cast on these tables so many times before to get these strings to a date value. Is there any other reason this could happen?

Or is there a way I can just make it look like its in the regular date time format for presentation purposes?

quote:
Originally posted by sross81

Ok i see what your saying. I hate how the users can enter the dates however they want! Is there a way to exclude that type of data or can I just not convert all of the data because of some?

quote:
Originally posted by Peso

No, NULL is not the problem.
A string like "20080431" is.

E 12°55'05.25"
N 56°04'39.16"

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:19:30

To which dates are not interpreted as dates, run

select col1 from table1
where isdate(col1) = 0

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-20 : 16:20:08

If you wanted you can use the ISDATE() function to find the rows that the date is invalid on and then exclude them.

EDIT: Peso beat me to it...

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 16:23:33

I ran the isdate() function. I got 1139 rows back that say the word NULL. So that has to be the problem. I just don't know how to exclude them.

I just realzied that I posted this in the sql 2000 forum intead of 2005. I have no idea how I managed to get into this one since I always go to the other one.... sorry for the confusion.

quote:
Originally posted by Peso

To which dates are not interpreted as dates, run

select col1 from table1
where isdate(col1) = 0

E 12°55'05.25"
N 56°04'39.16"

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:27:32

You still don't get it?

DECLARE @Sample TABLE (i varchar(20))

INSERT	@Sample
SELECT	'20080620' UNION ALL
SELECT	NULL

SELECT	i,
	CAST(i AS DATETIME)
FROM	@Sample

Works very well for NULL.

E 12°55'05.25"
N 56°04'39.16"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-20 : 16:27:42

Try putting "and col1 is not null" in your where clause and see if there are any other than nulls coming back.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:28:04

Also run this

select distinct col1 from table1
where isdate(col1) = 0

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 16:32:25

Its odd because I have 2 other fields that are formatted as string dates that have nulls in them that I am able to convert its just that appt_date one that doesn't work. I am not sure how to intergrate your code into mine. I have never used Union? Here is my code. Any tips on how to put yours into it?

select a.first_name + ' ' + a.last_name as PtName,
cast(a.date_of_birth as datetime) as PtDOB,c.enc_timestamp as DOV,
cast(b.ref_initiated_date as datetime) as DateProcessed,
b.appt_date,b.urgent_referral as Urgent,
b.ref_prov_first + ' ' + b.ref_prov_last as Primary_Provider,
b.referral_prov_first as Specialty_Provider,
b.specialty,
b.ref_initiatedby as ORW
from
person a
join chc_referral_form_ b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id

quote:
Originally posted by Peso

You still don't get it?
DECLARE @Sample TABLE (i varchar(20))

INSERT @Sample
SELECT '20080620' UNION ALL
SELECT NULL

SELECT i,
CAST(i AS DATETIME)
FROM @Sample
Works very well for NULL.

E 12°55'05.25"
N 56°04'39.16"

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:43:07

Run this query and post back the result.

SELECT	DISTINCT
	appt_date
FROM	[chc_referral_form_]
WHERE	ISDATE(appt_date) = 0

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 16:46:09

I get this back:

NULL
20080300

it looks like that 20080300 is a date that isn't real. It doesn't even let you enter a date like that because its chose by a calender.

quote:
Originally posted by Peso

Run this query and post back the result.
SELECT DISTINCT
appt_date
FROM [chc_referral_form_]
WHERE ISDATE(appt_date) = 0

E 12°55'05.25"
N 56°04'39.16"

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:54:33

You can manually edit the date to 20080301 if it doesn't matter.

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 16:57:13

I am trying to find it in the table right now. I went into the application and tried and something is wrong with that template and I can't edit it. I actually may not have privalges...not sure. But anyways how will I handle the NULL problem? All my other cast functions I am using are converting string dates regardless of whether null is in the field. I just can't figure out why it matters so much on this one. I am not sure how to do that NULL Union thing you showed me. I just have never done it before so not sure how to intergrate it into my code.

quote:
Originally posted by Peso

You can manually edit the date to 20080301 if it doesn't matter.

E 12°55'05.25"
N 56°04'39.16"

Thanks in Advance!
Sherri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 17:00:19

I meant an UPDATE because you can have several records with same value.

UPDATE	[chc_referral_form_]
SET	appt_date = '20080301'
WHERE	appt_date = '20080300'

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-20 : 17:01:56

quote:

But anyways how will I handle the NULL problem?

Again, NULL is the not the problem. The problem is the value of 20080300. That is not a valid date. There is no day "0" in the month of March for the year 2008. The days are numbered 1-31. Right? Thus, that date cannot be converted, you need to fix the data and change that value.

- Jeff
http://weblogs.sqlteam.com/JeffS

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-20 : 17:02:03

Null is not the problem. If it's null then it will convert it to null. That one record with 20080300 is what is messing the whole conversion up for that field. You will need to fix it first.

Edit: Guess I'm just slow today...Jeff beat me to it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 17:02:05

Too see how many faulty records you have of each type, run

SELECT		appt_date,
		COUNT(*) AS Records
FROM		[chc_referral_form_]
WHERE		ISDATE(appt_date) = 0
GROUP BY	appt_date
ORDER BY	appt_date

E 12°55'05.25"
N 56°04'39.16"

sross81
Posting Yak Master

228 Posts

Posted - 2008-06-20 : 17:03:15

I know you said NULL wasn't the problem at first but since it was coming back when I did that isdate() test I thought it had something to do with it. You guys are right thought I updated that field and now it works fine. Thanks for showing me how to do that isdate() test that will definitely come in useful in the future I am sure! :)

quote:
Originally posted by jsmith8858

quote:

But anyways how will I handle the NULL problem?

Again, NULL is the not the problem. The problem is the value of 20080300. That is not a valid date. There is no day "0" in the month of March for the year 2008. The days are numbered 1-31. Right? Thus, that date cannot be converted, you need to fix the data and change that value.

- Jeff
http://weblogs.sqlteam.com/JeffS

Thanks in Advance!
Sherri

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources