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
• 9,680 points
3,944 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,660 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,380 points
1,017 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 in Others by narikkadan
• 37,660 points
60 views
0 votes
1 answer
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
251 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 in Database by Kithuzzz
• 20,660 points
82 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 in Database by Kithuzzz
• 20,660 points
59 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 in Database by Kithuzzz
• 20,660 points
141 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 in Database by narikkadan
• 37,660 points
97 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,660 points
724 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,660 points
2,874 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