CODEWITHSUNDEEP
sqlsql-servert-sqldate
GeorgiG
GeorgiG

Reputation: 1101

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.

Upvotes: 1

Views: 3024

Answers (3)

Melissa Pazen
Melissa Pazen

Reputation: 77

Additional tip:

If you're using the CONVERT solution above, note that

"convert(varchar, CAST(dateadd(Hour, 2, getdate()) AS varchar), 101)"

calls for you to set datatype to varchar.

I just came across code

"Convert(date,ML.StartDate,101)"

and since style 101 is mm/dd/yyyy, and the output was yyyy-mm-dd, I knew something was wrong. By changing the code to

"Convert(varchar,ML.StartDate,101)"

the proper date style was displayed in the result set.

Upvotes: 1

Nebi
Nebi

Reputation: 326

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

CONVERT(data_type(length),expression,style)

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:

09/14/2016

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.

Upvotes: 1

LukStorms
LukStorms

Reputation: 29677

When you use convert to format the datetime, you can pass a style number to it.
Looky here or here for those numbers.

The query below converts custom created datetimes to the 126 (ISO8601) format.

declare @d int = 2;

SELECT 
CONVERT(varchar,
        CONVERT(datetime,
                CONCAT(FORMAT(GETDATE(),'yyyy-MM-dd'),' ',@d,':0')
                )
         ,126) AS MyDateStamp1,
CONVERT(varchar,
        CONVERT(datetime,
                CONVERT(varchar,GETDATE(),102)+' '+convert(varchar,@d)+':0'
                )
         ,126) AS MyDateStamp2;

The FORMAT & CONCAT functions can be used in SQL Server 2012 and beyond.
But if you have an earlier version then CONVERT should work instead.

Upvotes: 1

Related Questions