SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

0 votes

I have been getting the following error when running a SQL to convert my data type value from varchar to datetime.

Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


I have checked the data and can't see anything to add as I Ran the following checks and all are returning no results:-

SELECT [Date] from table where [DATe] is null 
SELECT [Date] from table where [DATe] = '' 
SELECT [Date] from table where LEN([date])> 10 
SELECT [Date] from table where LEN([date])< 10 
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int,SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12 
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31

Is there anything else worth looking at or helping with this issue? Cannot seem to find a way.

Feb 23 in Others by Rahul
• 8,980 points
57 views

1 answer to this question.

0 votes

 I ran into the same problem due to a very minimal and careless mistake. Make sure the date actually exists

For example:

September 31, 2015 does not exist.
 

EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150931'

So this fails with the message:
 

Error converting data type varchar to datetime.

To fix it, input a valid date:
 

EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150930'

This will ensure it works just about fine.

answered Feb 23 by Aditya
• 7,280 points

Related Questions In Others

0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,340 points
665 views
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
175 views
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
611 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 5,040 points
35 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 5,040 points
12 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
74 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
77 views
0 votes
1 answer

How to create an HTML button that acts like a link

To answer your doubt, the plain HTML ...READ MORE

answered Feb 17 in Others by Aditya
• 7,280 points
25 views
0 votes
1 answer

Get selected value of a dropdown's item using jQuery

To answer your question, start with using ...READ MORE

answered Feb 18 in Others by Aditya
• 7,280 points
39 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP