Solved

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

Posted on 2013-12-12
7
732 Views
Last Modified: 2013-12-13
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
0
Comment
Question by:ghettocounselor
  • 3
  • 3
7 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 39715121
You most probably have a wrong date value in the column CHARTED_DATE. Try selecting all values in use and checking them manually.
0
 

Author Comment

by:ghettocounselor
ID: 39715123
yep, done that and there's nothing visually incorrect in the data
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 39715129
Is it a lot of data? Can you post the values of the column?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:ghettocounselor
ID: 39715180
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
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 39715346
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?
0
 

Author Comment

by:ghettocounselor
ID: 39715357
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

0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39715434
You don't need any convertion. Use
AND CHARTED_DAT BETWEEN '20130601' AND '20131231'

Open in new window

as that format is well suited to be queried directly (sort order is correct).
But if you need to convert for whichever reason, do it right. The style parameter of 101 is mm/dd/yyyy - and that certainly is not the format of your field - 112 is the right one!
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL Default value in Select? 5 38
T-SQL: New to using transactions 9 46
SQL Recursion schedule 13 15
Reformat SQL - so SSRS can read the columns 25 12
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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