Showing posts with label DATATYPE. Show all posts
Showing posts with label DATATYPE. Show all posts

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


So while running a SQL query in SQL Server you may find this error :


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


After trying multiple formats was able to execute and  found that the reason behind this error was tryingMYSQL Datetime formate instead Microsoft SQL server:



For an Example, just take below SQL query and try to understand.


------------------------------------------------------------>
First, create a table:


Create table professionals (Pro_ID int primary key, First_Name varchar(30), Last_Name Varchar(30),  Date_Join datetime, Profession varchar(30));


So in above example when you are going to enter the date and time in Date_Join in MySQL formate which is in bold below:


MySQL FORMAT:
insert into professionals(Pro_ID, First_name, Last_name, Salary, Date_Join, Profession)
Values
(2, 'Steve', 'Jobs', 1500000, '1983-02-04 09.00.00', 'CEO')


_________________MS SQL FORMAT BELOW_________________


It will not accept it because it needs a MS SQL Server Format which is below:


MS SQL SERVER FORMAT:
(2, 'Steve', 'Jobs', 1500000, '1983-02-04 09:00:00:000', 'CEO')


Hopefully, this is going to save a lot of trouble for you.


Enter your email address:

Delivered by FeedBurner