Solved

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

Posted on 2013-12-12
7
724 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
Comment Utility
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
Comment Utility
yep, done that and there's nothing visually incorrect in the data
0
 
LVL 20

Expert Comment

by:TheAvenger
Comment Utility
Is it a lot of data? Can you post the values of the column?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ghettocounselor
Comment Utility
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
Comment Utility
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
Comment Utility
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 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now