Converting data types in SQL Server using CAST

ryoqjall 于 5个月前发布在 SQL Server

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

I'm new to SQL Server and I would like to know how to convert data types in SQL Server. As far as I know SQL Server doesn't support format mask (ex.'YYYY-MM-DD' or '999'). It's basically about all data types, not only DATE data type as below.

Let's say that I want to convert '20240512' to 2024-12-05, NOT default one: 2024-05-12.

In Oracle I can use TO_DATE and CAST - both functions support mask.

SELECT 
    TO_DATE('20240512')      AS wrong1,
    CAST('20240512' AS DATE) AS wrong2,
    TO_DATE('20240512', 'YYYYDDMM') AS correct1,
    CAST('20240512' AS DATE, 'YYYYDDMM') AS correct2;

In Postgres I can use TO_DATE . CAST doesn't support format mask.

And what about SQL Server, when TO_DATE doesn't exists and CAST doesn't support format mask?

sql-server

来源：https://stackoverflow.com/questions/77613901/converting-data-types-in-sql-server-using-cast

1条答案

按热度按时间

gkl3eglg1#

To summarize the comment's as an answer:

YYYYMMDD is a very specific format used to avoid localization issues. Same with the ISO8601 form, YYYY-MM-DD . - panagiotis-kanavos

in TSQL you can use CAST which is ANSI-SQL standard, or the more flexible CONVERT - CAST and CONVERT (Transact-SQL) . CONVERT accepts a style parameter when converting to a string: SELECT CONVERT(VARCHAR(8), GETDATE(), 112) . It is worth pointing out that formatting a date does make it a string, as a DATE does not have a format. – Patrick Hurst

Looks like OP needs yyyyddmm which is not an available style. So maybe use FORMAT . – Charlieface

you can't convert 12345678 to a date either. In the case you specify you're looking for the database to perform your ETL sanitation, which isn't really it's job. If you really want to convert 'YYYYDDMM' to a DATE you should be parsing the input to be able to provide it in a standard order, but it is possible: SELECT '20233011', TRY_CAST('20230311' AS DATE), CAST(LEFT('20230311',4)+RIGHT('20230311',2)+SUBSTRING('20230311',5,2) AS DATE) – Patrick Hurst

Sql Server doesn't support format mask it does, using the CONVERT , PARSE , or FORMAT functions. The conversion you ask for though is essentially ..... evil. It will confuse every single developer and even yourself. By next month if not next Monday you won't remember you used a custom format instead of the standard one you see everywhere. You will convert 20240104 to April 1. – panagiotis-kanavos

赞(0）回复(0）举报 5个月前

我来回答

Converting data types in SQL Server using CAST

1条答案

相关问题

热门标签

最新问答