I have a column named "Period". In this column, year and month are entered consecutively. For example, in "201803", 2018 represents the year and 03 represents March. I want to create a date column in the format of "01.03.2018" using this column. Day should be only 01.
When I use the following code,
CONCAT(RIGHT('00' + CAST(RIGHT([Period], 2) AS VARCHAR(2)), 2), '-01-', CAST(Left([Period], 4) AS VARCHAR(4)))it works, but I see the date in the format of "03.01.2018", meaning the day and month are swapped.
However, when I enter the code in the following way;
'01.' + RIGHT('00' + CAST(RIGHT( RIGHT([Period], 2) , 2) AS VARCHAR(2)), 2) + '.' + CAST(LEFT( LEFT([Period], 4) , 4) AS VARCHAR(4))I receive the following error.
Error converting a string to a date and/or time.
How can I solve this issue in T-SQL?
2条答案
按热度按时间t30tvxxf1#
yyyyMMddis already an unambiguous date format for all date and time data types in SQL Server, so just add'01'to the end of the string. As, however, storing "date" values in a non-date and time data type tends to also result in bad data (like202713) then you'll likely want to useTRY_CONVERTtoo:rkue9o1l2#
You can also use the below to get the output like "01.03.2018":