?
Solved

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

Posted on 2013-12-12
7
Medium Priority
?
742 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 71

Accepted Solution

by:
Qlemo earned 2000 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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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