CODEWITHSUNDEEP
sqlsql-serverdatetime
Haz
Haz

Reputation: 361

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

I'm getting the conversion error when converting below value.`

select CONVERT(datetime, LEFT(' September 2, 2019 14:29:46', 21), 101)

but I can convert the below value without an error 

select CONVERT(datetime, LEFT(' August 31, 2019 11:43:44', 21), 101)

It really thankful if someone can help.

Upvotes: 0

Views: 437

Answers (4)

DarkRob
DarkRob

Reputation: 3833

If want to use OP approach, you should subtract your length of string with the time part, as time part is always fixed while length of month name is almost vary every month.


    DECLARE @ReportStartDate varchar(30) = 'September 2, 2019 14:29:46'
    select  convert ( datetime, left( @ReportStartDate, len(@ReportStartDate) - 9), 101)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

SQL Server is actually pretty good at picking out date formats when there is no conversion specifier. Because your dates have month names, this is quite safe with your strings.

So, you can just do:

select convert(datetime, ' September 2, 2019 14:29:46'),
       convert(datetime, ' August 31, 2019 11:43:44')

Personally, I would use TRY_CONVERT() instead:

select try_convert(datetime, ' September 2, 2019 14:29:46'),
       try_convert(datetime, ' August 31, 2019 11:43:44')

Upvotes: 1

Thom A
Thom A

Reputation: 95554

That isn't the style 101, it's the style 100 (101 is MM/dd/yyyy). You also have a leading ' ' that you should to remove:

SELECT CONVERT(datetime,STUFF(' September 2, 2019 14:29:46',1,1,''),100);

Note, as well, that this'll fail if you aren't using an English derived language DB<>Fiddle. Passing the name if a month for a date is one of the worst ways to pass a date/time value, as if you change language it will no longer work.

Even though an unambiguous format (yyyyMMdd or yyyy-MM-ddThh:mm:ss.sss) is best, if you are going to use an ambiguous one at least use one that doesn't provide the name of the month and only uses numerical characters to provide the date.

Upvotes: 2

iamdave
iamdave

Reputation: 12243

The left(...,21) for both your values results in the following:

LEFT(' September 2, 2019 14:29:46', 21) -> September 2, 2019 14
LEFT(' August 31, 2019 11:43:44', 21)   -> August 31, 2019 11:4

Hopefully from this you can see why your left conversion logic is flawed and why it is failing.

As your values are already in a recognised date value format, you don't actually need to modify them at all:

select CONVERT(datetime, ' September 2, 2019 14:29:46', 100)
      ,CONVERT(datetime, ' August 31, 2019 11:43:44', 100)

returns:

2019-09-02 14:29:46.000
2019-08-31 11:43:44.000

Upvotes: 2

Related Questions