Solved

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

Posted on 2013-12-12
7
735 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
[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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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