Solution: SQL Server
In any migration project, the transformation of dates is a common task. There will be many occasions, when you will have to change the format to fit with the target requirement or to compare dates.
I have frequently seen that less experienced analyst with SQL skills get usually confused in how to easily manipulate the data by just using standard SQL functions.
Let's start with an example.
How would you change '21.1.2017' string to a date with the following format YYYYMMDD?
There would be many people who use the combination of LEFT() and RIGHT(), or combinations of SUBSTRING() and some other just CONVERT() or CAST().
I personally do not like using string manipulations like LEFT(),RIGTH() or SUBSTRING because when you have thousands of lines you may find some inconsistences like '21.1.2017' or '21.01.2017', which would make the logic more difficult.
I find that the easiest way is to use CONVERT(). If you are not familiar with this function:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
- data_type
Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
- length
Is an optional integer that specifies the length of the target data type. The default value is 30.
- expression
Is any valid expression.
- style
Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type.
But please you need to be very careful, the most important of this post is to remember this:
The style is the Input when you convert to datetime; output when you convert to character data.
What does it mean?
- Input when you convert to datetime
In this scenario, the style indicates what is the string style that you are using as input to convert into a datetime. This mean that data_type has to be a date and the expression a string.
In our example, we are using '21.1.2017', so the style is 104 (dd.mm.yyyy) because refers to the input.
CONVERT(date,'21.1.2017',104)
This function will convert the string 21.1.2017 into a dateformat, result:
Note that SQL display on screen this date as YYYY-MM-DD (ISO), this is due to the SQL set up itself.
If you do not specify the format, SQL use the local configuration to determine the input format, in the below example you can see that for us_english is mdy, so a conversion doing convert(date,'1/21/18') (mdy) is correctly interpreted but convert(date,'21/1/18') (dmy) generates an error.
- Output when you convert to character data
In this scenario, the style indicates in what style you want to see the string result. This mean that data_type has to be a string and the expression a date.
In our example, the expression is a string, so we first need to convert to a date (what we have done in the previous example) and then, convert it to a string with the style YYYYMMDD, which is the 112 because it refers to the output.
CONVERT(NVARCHAR(8),(CONVERT(DATE,'21.1.2017',104)),112)
[Added on 08/10/2018]
I would like to add an importan piece of information when you work two-digit years. Accordingly to Microsoft documentation;
By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That means that SQL Server interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950. Many client applications, including those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option to change the cutoff year used by SQL Server. This allows for the consistent treatment of dates. We recommend specifying four-digit years.
This is very important, specially when you are working with future dates or when you have not profiled the data to determine the range that you are working with. As Microsoft suggest, try to use 4 digits to specify the year.
I hope you have found this post interesting.
Please do not hesitate to ask any questions.
Comments
0 comments