Link to home
Start Free TrialLog in
Avatar of ghettocounselor
ghettocounselorFlag for United States of America

asked on

Conversion failed when converting date and/or time from character string

I get this message "Conversion failed when converting date and/or time from character string." when executing my query with this criteria

AND CONVERT(DATETIME, CHARTED_DATE, 101) BETWEEN '2013-06-01' AND '2013-12-31'

I've never had this problem before and have used this syntax repeatedly, is there some kind of check of the data I should be performing in this syntax to have only items which meet a date/time
Avatar of TheAvenger
TheAvenger
Flag of Switzerland image
You most probably have a wrong date value in the column CHARTED_DATE. Try selecting all values in use and checking them manually.
Avatar of ghettocounselor
ghettocounselor
Flag of United States of America image

ASKER

yep, done that and there's nothing visually incorrect in the data
Avatar of TheAvenger
TheAvenger
Flag of Switzerland image
Is it a lot of data? Can you post the values of the column?
Avatar of ghettocounselor
ghettocounselor
Flag of United States of America image

ASKER

CHARTED_DATE
20130612
20130612
20130613
20130613
20130615
20130615
20130616
20130822
20130822
20130823
20130519
20130520
20130528
20130529
20130529
20130907
20130907
20130909
20130909
20130911
20130911
20130912
20130315
20130315
20130317
20130317
20130724
20130725
20130725
20130726
20130726
20130727
20130727
20130728
20130728
20130730
20130730
20130731
20130408
20130408
20130904
20130905
20130726
20130727
20130727
20130728
20130728
20130730
20130730
20130731
20130731
20130802
20131106
20131107
20131107
20131108
20131108
20131109
20131109
20131110
20131110
20131111
20131111
20131112
20131112
20131113
20131113
20131114
20131114
20131115
20131115
20131116
20131116
20131117
20130823
20130823
20130824
20130824
20130825
20130825
20130826
20130826
20130828
20130828
20130831
20130831
20130902
20130902
20130903
20130903
20130904
20130904
20130905
20130916
20130918
20130918
20130919
20130919
20130921
20130921
20131207
20131207
20131208
20131208
20131209
20131209
20131210
20131210
20131211
20131211
20130522
20130523
20130523
20130525
20130525
20131014
20131014
20131015
20131015
20131017
20131017
20131018
20131018
20131019
20131019
20131020
20131020
20131105
20131106
20131106
20131107
20131107
20131108
20131108
20131109
20131109
20131110
20131110
20131111
20131111
20131112
20131112
20131113
20131113
20131114
20131114
20131115
20131125
20131126
20131126
20131127
20131127
20131128
20131128
20131129
20131129
20131129
20131129
20131129
20131129
20131202
20131202
20131203
20131203
20131203
20131203
20131204
20131204
20131205
20131205
20131207
20131207
20131208
20131208
20131209
20131209
20131210
20130514
20130517
20130517
20130518
20130518
20130519
20130519
20130520
20130520
20130521
20130521
20130522
20130523
20130523
20130610
20130611
20130611
20130824
20130824
20130825
20130825
20130826
20130826
Avatar of TheAvenger
TheAvenger
Flag of Switzerland image
This data is OK and the convert works fine.

You initial SQL seems to be a part of a bigger query. Could it be that another part is failing?
Avatar of ghettocounselor
ghettocounselor
Flag of United States of America image

ASKER

The odd thing is that this query works fine with the date bit commented out, with it in the error message above shows up. 

SELECT LOCATION, ADMIN_STATUS, CHARTED_DATE, CHARTED_TIME, PAT_NUM, PTNAME, DRUG_INFO_1, DRUG_CODE_LIST, 
	MONTH(CHARTED_DATE) AS MONTH_CHARTED
	INTO #tmp_PHM_MAC_CHRT_RECORDS
	FROM PHM_MAC_CHRT_Trns	
	WHERE DRUG_CODE_LIST LIKE ',3063,' AND ADMIN_STATUS NOT LIKE 'NOTADMIN'
	--AND CONVERT(DATETIME, CHARTED_DATE, 101) BETWEEN '2013-06-01' AND '2013-12-31'
	ORDER BY PTNAME, CHARTED_DATE, CHARTED_TIME
	SELECT * FROM #tmp_PHM_MAC_CHRT_RECORDS

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial