CODEWITHSUNDEEP
sql-server
Geetha Anand
Geetha Anand

Reputation: 73

MS SQL error : conversion of a varchar data type to a datetime data type resulted error

I am using MS SQL Server. One table column is defined as order_date varchar(25) and is stored in a format like 05/11/2015 07:54:16

In my select query, I am trying to convert that into a date format like (yyyy-mm-dd HH:MM:SS:000, e.g. 2015-05-11 08:03:10.000

I have tried with

select CONVERT(varchar(50), CAST(order_date AS datetime),121) from <table>

In my table I have around 500 records, but after fetching 10 records in my expected format, I get this error error:

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

Is there any issue with my conversion?

Upvotes: 0

Views: 1450

Answers (2)

user5386938
user5386938

Reputation:

I'd try to see what the value of order_date is at the time of the error. Perhaps what you assume to be a datetime string in mm/dd/yyyy... format is actually in dd/mm/yyyy....

I.e., the following outputs 2015-05-11 07:54:16.000 as my system is set to datetime format of mm/dd/yyyy.

    declare @order_date varchar(100) = '05/11/2015 07:54:16'; -- mm/dd/yyyy...
    print CONVERT(varchar(50), CAST(@order_date AS datetime),121);

the following throws an error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    set @order_date = '13/09/2015 07:54:16'; -- dd/mm/yyyy...
    print CONVERT(varchar(50), CAST(@order_date AS datetime),121);

Upvotes: 0

Thom A
Thom A

Reputation: 95561

There are a couple of problems here. The first is your data type choice, but I'll just repeat my comment for that: ""am trying to convert that into date format "* This is totally the wrong approach. Stop storing dates as a varchar use a date and time data type. The reason you have this error is because your poor data type choices. Fix that and get the presentation layer to work about the formatting."

Now, moving on. You have your expression below:

CONVERT(varchar(50), CAST(order_date AS datetime),121)

Firstly, as your value is a varchar, you need to tell SQL Server the format it is in; dd/MM/yyyy hh:mm:ss (I guess as '05/11' is ambigous)) is not unambiguous. What you have is the UK style, which is style 103:

CONVERT(datetime,'05/11/2015 07:54:16',103)

Now you can convert that to your ISO format:

CONVERT(varchar(23),CONVERT(datetime,'05/11/2015 07:54:16',103),121)

This returns the varchar value '2015-11-05 07:54:16.000'

Upvotes: 2

Related Questions