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, 2022 in Others by Rahul
• 9,680 points
10,964 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, 2022 by Aditya
• 7,680 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,460 points
1,810 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
793 views
0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
1,382 views
0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,600 points
884 views
0 votes
0 answers

DateTime2 vs DateTime in SQL Server

Which one: datetime datetime2 is the recommended way to store date and ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,000 points
610 views
0 votes
0 answers

How can I select the first day of a month in SQL?

I only need to choose the given ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,000 points
638 views
0 votes
0 answers

How to get Time from DateTime format in SQL?

I want to use a SQL query ...READ MORE

Aug 15, 2022 in Database by Kithuzzz
• 38,000 points
926 views
0 votes
1 answer

SQL Server string to date conversion

Try this Cast('7/7/2011' as datetime) And Convert(DATETIME, '7/7/2011', 101) See CAST and ...READ MORE

answered Sep 16, 2022 in Database by narikkadan
• 63,600 points
2,491 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, 2022 in Others by Aditya
• 7,680 points
4,423 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, 2022 in Others by Aditya
• 7,680 points
13,685 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP