GeorgiG GeorgiG - 1 year ago 69
SQL Question

Convert date format doesn't take effect on self made date string in SQL Server

I have a rather strange issue here. I have a date string, which I've created partly by myself to incorporate a variable. The problem is, that I'm setting another language settings. In this case, I have to also convert the string to fit the language settings format. I'm using this code:

cast(convert(varchar, cast(cast(getdate() as date) as varchar) + ' ' + RIGHT('0' + CAST(@HR as varchar), 2) + ':00:00.000', 120) as datetime)

I get the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.", which is normal if you assign wrong date format before casting.

The problem is, that when I try to convert the personally created date string, it doesn't change its format no matter what format code I set it in. That doesn't change even when I hardcode a number instead of my variable:

convert(varchar, cast(cast(getdate() as date) as varchar) + ' 0' + CAST(2 as varchar) + ':00:00.000', 101)

results in 2016-09-14 02:00:00.000

For example,

convert(varchar, dateadd(Hour, 2, getdate()), 101) as datetime

Results in 09/14/2016.

Even though I have a different language setting, isn't SQL server supposed to always recognize the date format in the standard format?

Please give me an advice so I can overcome this issue.

Thanks in advance!

PS: I managed to solve my issue by inserting converted datetime column in a variable before setting bulgarian language. I'm still very interested to know what causes the problem though.

Answer Source

Ok I may have a soution for the question: Why is the format differently handled in SQL-SERVER when converting.


The STYLEvalue only applies for date/time. So it's because of the datatype that the output is different. See following example:

SELECT convert(varchar, dateadd(Hour, 2, getdate()), 101) as datetime

You get the result:


Here your are converting a datetime datatype into a varchar and the STYLE-value with 101 applies for CONVERT and the output is converted in that format.

Example 2 is the same but the inner most part is casted into a varchar before converting it:

SELECT convert(varchar, CAST(dateadd(Hour, 2, getdate()) AS varchar), 101) as datetime

The result you get is:

Sep 14 2016  4:09PM

So because we are trying to convert a varchar into a varchar the STYLE-value doesn't apply for the conversion.

That is also why the first query is handled diffrent then the other:

SELECT convert(varchar, cast(cast(getdate() as date) as varchar) + ' 0' + CAST(2 as varchar) + ':00:00.000', 101)

Here you cast into varchar cast(cast(getdate() as date) as varchar) before converting. So the STYLE-value is not applying because it's not from datatype date/time.

I hope it made it a bit clear. Let me know if this helped.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download