Skip to content
Fix Code Error

Conversion failed when converting date and/or time from character string while inserting datetime

March 13, 2021 by Code Error
Posted By: Anonymous

I was trying to create a table as follows,

create table table1(date1 datetime,date2 datetime);

First I tried inserting values as below,

insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');

It has given error saying,

Cannot convert varchar to datetime

Then I tried below format as one of the post suggested by our stackoverflow,

insert into table1 values(convert(datetime,'21-02-2012 6:10:00 PM',5)
                          ,convert(datetime,'01-01-2001 12:00:00 AM',5));

But am still getting the error saying,

Conversion failed when converting date and/or time from character string

Any suggestions?

Solution

There are many formats supported by SQL Server – see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have – therefore, these settings might work some times – and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server – this format works always – regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that’s very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times – note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

So in your concrete case – use these strings:

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

and you should be fine (note: you need to use the international 24-hour format rather than 12-hour AM/PM format for this).

Alternatively: if you’re on SQL Server 2008 or newer, you could also use the DATETIME2 datatype (instead of plain DATETIME) and your current INSERT would just work without any problems! 🙂 DATETIME2 is a lot better and a lot less picky on conversions – and it’s the recommend date/time data types for SQL Server 2008 or newer anyway.

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine  

Don’t ask me why this whole topic is so tricky and somewhat confusing – that’s just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

Answered By: Anonymous

Related Articles

  • How to check if a date is greater than another in Java?
  • How do I include certain conditions in SQL Count
  • warning: control reaches end of non-void function…
  • Reference - What does this regex mean?
  • mongodb group values by multiple fields
  • Log record changes in SQL server in an audit table
  • Getting the difference between two Dates…
  • setTimeout function not working : javascript
  • Ukkonen's suffix tree algorithm in plain English
  • Can't find why the if block not getting executed
  • What is the origin of foo and bar?
  • adding a Fix value into date column as running value…
  • MS SQL compare dates?
  • Conversion failed when converting the varchar value…
  • Convert Java Date to UTC String
  • How do I count unique visitors to my site?
  • AppCompat v7 r21 returning error in values.xml?
  • SQL query return data from multiple tables
  • SQL NVARCHAR and VARCHAR Limits
  • Current time formatting with Javascript
  • SQL MAX of multiple columns?
  • data.table vs dplyr: can one do something well the…
  • SQL Transaction Error: The current transaction…
  • What is the worst programming language you ever worked with?
  • Reference — What does this symbol mean in PHP?
  • How do I obtain a Query Execution Plan in SQL Server?
  • Difference in months between two dates
  • How to Select Min and Max date values in Linq Query
  • Comparing two java.util.Dates to see if they are in…
  • Conversion of a varchar data type to a datetime data…
  • Using StringWriter for XML Serialization
  • Convert Python dict into a dataframe
  • How to do data binding in polymer with meteor
  • What are the undocumented features and limitations…
  • How to convert java.util.Date to java.sql.Date?
  • How to query for Xml values and attributes from…
  • Remove pandas rows with duplicate indices
  • Difficulty Importing a txt file into a Pandas Dataframe
  • CSS Float: Floating an image to the left of the text
  • Extract month and year from a zoo::yearmon object
  • Use SQL Server Management Studio to connect remotely…
  • How to show title in hover - css / jquery
  • how to fix Invalid request (Unsupported SSL request)…
  • java Compare two dates
  • Improve INSERT-per-second performance of SQLite
  • Drop all the tables, stored procedures, triggers,…
  • How to make dependent fetch API calls in Next.js
  • How to conditionally select the first non null date…
  • PHP mysql insert date format
  • What does this symbol mean in JavaScript?
  • No function matches the given name and argument types
  • java.sql.SQLException: Missing IN or OUT parameter…
  • Failed when converting the nvarchar value to to data…
  • Calculate time difference in minutes in SQL Server
  • Select SQL Server database size
  • Meaning of @classmethod and @staticmethod for beginner?
  • Is this request generated by EF Core buggy or is it my code?
  • Operand type clash: int is incompatible with date +…
  • Best practices for SQL varchar column length
  • How to compare two dates in php
  • How to compare two NSDates: Which is more recent?
  • How do I filter an array with TypeScript in Angular 2?
  • Java string to date conversion
  • "Thinking in AngularJS" if I have a jQuery background?
  • SQL Server JOIN missing NULL values
  • What is the cause of [Vue warn]: Invalid prop:…
  • Backbone: Adding a model to Collection and render a View
  • The definitive guide to form-based website authentication
  • Interrupt an earlier timeout event in Simpy
  • React Button that Submits Express Backend Post…
  • Maximum size of a varchar(max) variable
  • sqldf only returning one row, same query used in SQL
  • How to fix repeating data from SQL join?
  • How can I find the product GUID of an installed MSI setup?
  • How do I convert the date from one format to another…
  • T-SQL How to create tables dynamically in stored procedures?
  • Understand Backbone.js REST calls
  • Merge on specific column with multiple conditions
  • Generate SQL Create Scripts for existing tables with Query
  • What is a NullReferenceException, and how do I fix it?
  • What is the copy-and-swap idiom?
  • Declaring & Setting Variables in a Select Statement
  • Angular: Can't find Promise, Map, Set and Iterator
  • How to do a SQL NOT NULL with a DateTime?
  • Hibernate table not mapped error in HQL query
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • SSMS :: Copy with Headers and paste to Excel results…
  • Swift 3 - Comparing Date objects
  • Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'
  • biblatex: splitting bibliography entry which are…
  • how to get a date a week from now on in python?
  • Converting Oracle SQL Procedure into MySQL Stored Procedure
  • Remove elements from collection while iterating
  • How can I truncate a datetime in SQL Server?
  • Using DateTime in a SqlParameter for Stored…
  • Fatal error: [] operator not supported for strings
  • Type datetime for input parameter in procedure
  • Get difference between 2 dates in JavaScript?
  • How to extract object properties from a JSON response
  • How to find column names for all tables in all…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How do I pass data between Activities in Android application?

Next Post:

How to list the tables in a SQLite database file that was opened with ATTACH?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error