Convert function is not working with {fn } in SQL Server

o2rvlv0m 于 2023-03-22 发布在 SQL Server

关注(0)|答案(1)|浏览(107)

I have a development SQL Server, "Microsoft SQL Server 2019 (RTM-GDR) (KB4583458)"

If I use the convert function with {fn} then it's working, as expected:

SELECT CONVERT(DATETIME, {fn CURRENT_TIMESTAMP()})

Now, the production database is on "Microsoft SQL Server 2019 (RTM-GDR) (KB5021125)"

Translating the error:
When converting from a varchar data type to a datetime data type, the value is out of range.

Is this a patch bug, and is there a way to check the definition of the convert function in the SQL Server?

1条答案

No, it's not a bug. Your production server is set to a different language, or the DATEFORMAT setting is different.

You need to use an explicit conversion style to get the right results, 121 is the one you are looking for.

SELECT CONVERT(datetime, {fn CURRENT_TIMESTAMP()}, 121);

But you might as well use the normal syntax and get a datetime result in the first place.

SELECT CURRENT_TIMESTAMP();