Conversion of a Varchar Data Type to a Datetime Data Type Resulted in an Out-Of-Range Value

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

There are two issues in you query in the insert part of the statement. Firstly time separator should be (:) not dot (.) secondly the year should be written in in full for example '20-02-14 09:00:00' should change to '2020-02-14 09:00:00'

Your amended query below
CREATE TABLE Worker 
(
    WORKER_ID INT NOT NULL PRIMARY KEY,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT,
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
        (001, 'Monika', 'Arora', 100000, '2020-02-14 09:00:00', 'HR'),
        (002, 'Niharika', 'Verma', 80000, '2011-06-14 09:00:00', 'Admin'),
        (003, 'Vishal', 'Singhal', 300000, '2020-02-14 09:00:00', 'HR'),
        (004, 'Amitabh', 'Singh', 500000, '2020-02-14 09:00:00', 'Admin'),
        (005, 'Vivek', 'Bhati', 500000, '2011-06-14 09:00:00', 'Admin'),
        (006, 'Vipul', 'Diwan', 200000, '2011-06-14 09:00:00', 'Account'),
        (007, 'Satish', 'Kumar', 75000, '2020-01-14 09:00:00', 'Account'),
        (008, 'Geetika', 'Chauhan', 90000, '2011-04-14 09:00:00', 'Admin');

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query

Try ISDATE() function in SQL Server. If 1, select valid date. If 0 selects invalid dates.

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)  
FROM AuditTrail 
WHERE ISDATE(LoginTime) = 1

• Click here to view result

EDIT :

As per your update i need to extract the date only and remove the time, then you could simply use the inner CONVERT

SELECT CONVERT(VARCHAR, LoginTime, 101) FROM AuditTrail 

or

SELECT LEFT(LoginTime,10) FROM AuditTrail

EDIT 2 :

The major reason for the error will be in your date in WHERE clause.ie,

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)  
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <= 
CAST('06/18/2012' AS DATE)

will be different from

SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)  
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <= 
CAST('18/06/2012' AS DATE)

CONCLUSION

In EDIT 2 the first query tries to filter in mm/dd/yyyy format, while the second query tries to filter in dd/mm/yyyy format. Either of them will fail and throws error

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

So please make sure to filter date either with mm/dd/yyyy or with dd/mm/yyyy format, whichever works in your db.

Error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. while store date formate is dd-mm-yyyy

Convert your strings to dates indicating their format.

Insert into Pedido (FechaPedido, FechaTipoPedido) 
Values(convert(date, '13-03-2018', 105), convert(date, '13-03-2018', 105))

Concretely you are using the format 105 : https://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx

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

The reason you get this error is because the system doesn't recognize dd-mm-yyyy as a date format by default.

To solve this you have 2 options:

1. You can use set dateformat dmy at the top of your data insert file.

2. But a better approach would be to use yyyy-mm-dd format.

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

When you are inserting date and time values into SQL Server, you should always write your date strings in a region-neutral format.

INSERT INTO #temp_conv_data (company_code, source_currency, effective_date, conversion_factor, create_time, revision_time, from_web_services) 
VALUES ('FGLS', 'ADP', '20210120 18:00:00',  0.00729, '20200817 02:15:53', '20200817 02:15:53', 0)

Note the complete lack of slashes and dashes. This is the only format that works reliably on all SQL Server date and time related types, without worrying about region settings, apart from using the full ISO8601T format. If you use that format, it has to be the whole format, including the T. But the format I've suggested above is the easiest.

Note that as Nick mentioned, you also used month/day in different order in different constants. That was never going to work, regardless of regional setting.

ERROR: varchar data type to a datetime data type resulted in error out-of-range value

First, you seem to want to convert a string to a datetime value. Not the other way around.

Second, SQL Server is pretty good about converting date/time values. But commas do confuse it.

So, I think this does what you want:

CONVERT(DATETIME, REPLACE(XDateUpdated, ',', '.')) AS XDateUpdated

How to solve The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. error

Most likely SQL Server is trying to parse your data in another format that you are providing.

You can set the format with one of the values from this table:

convert(datetime, '2016-04-21 20:13:08.280', 121)

121 = yyyy-mm-dd hh:mi:ss.mmm(24h)

---

Related Topics