Problem
A business analyst recently asked me three questions. The questions are: How do I convert a mmddyyyy string to a SQL Server date? How do I handle rows with missing date strings? How do I compare a converted date string from one SELECT statement to another converted date string in another SELECT statement? This tip describes some T-SQL techniques for answering these questions.
Solution
There are at least a couple of great SQL SERVER functions (CAST and CONVERT) for converting between date formats and numerous MSSQLTips articles illustrating how to process dates with these functions; see the Next Steps sections for specific tips on the functions and additional MSSQLTips resources. However, sometimes just referring a colleague to the functions or articles is not sufficient help to enable them make SQL Server do what they need done. This article drills down on three specific questions. If you have a colleague with questions like these, the techniques in this tip may just turn you into a SQL Server hero in the eyes of your colleague.
The questions didn’t actually arrive all at once. Instead, they arrived in sequence. The colleague tried the answer to the first which worked for one column of dates, but then did not work for a second column. Eventually, the failure of the first answer resulted in the second question. After the colleague had a successful approach for processing two converted dates on each row, he was still challenged by how to compare the two. This lead to the final question and its answer.
The answers illustrated in this article depend on custom T-SQL code. I tried using built-in SQL Server functions, but I found remembering the special parameters more difficult for me to remember than just directly coding the solution. Furthermore, after I developed the solutions, I found it was easier to adapt them as follow-on questions emerged. In the end, the custom approach provided a relatively robust sets of solutions that was well adapted to the specific source data used in our project and the typical kinds of questions by colleagues for that data.
How do I convert an mmddyyyy string to a SQL Server date?
A quick web search did not yield a built-in SQL Server function that was exactly appropriate for converting an mmddyyyy text string to a SQL Server date. Some built-in functions discovered by the web search were almost appropriate so that modifying our original data would make them work after a transformation of the source data. In the end, a custom function was used that modified the source format, relied on the CAST function, and the date data type.
The built-in CAST function can take two arguments (an expression and a SQL Server data type) and return a value in the data type. For our data source, the original mmddyyyy format can be re-formatted as yyyy-mm-dd. The re-formatted representation of the source date designates a SQL Server date value. The following script shows the T-SQL to create the function and re-format the source data.
- The RIGHT function extracts the yyyy in the source data.
- The LEFT function extracts the mm in the source data.
- The SUBSTRING function extracts the dd in the source data.
- The expression (RIGHT(@MMDDYYYY,4)+’-‘+LEFT(@MMDDYYYY,2)+’-‘+SUBSTRING(@MMDDYYYY,3,2)) represents the re-formatted source data.
- The keyword AS is required with the CAST function before the returned data type.
CREATE FUNCTION [dbo].[MMDDYYYY_TO_SQL_DATE] ( @MMDDYYYY char(8) ) RETURNS date AS BEGIN DECLARE @date_out date SET @date_out = CAST(RIGHT(@MMDDYYYY,4)+'-'+LEFT(@MMDDYYYY,2)+'-'+SUBSTRING(@MMDDYYYY,3,2) as date) RETURN @date_out END
You can specify a preceding USE statement to designate the database in which you want to create the custom function. You must create the function inside of each database in which you want to use it before referencing the functions. The following script shows the application of the MMDDYYYY_TO_SQL_DATE user-defined function in the Staging database to transform a column of ASSIGN DATE values from the #MSQLTips_MMDDYYYY_Dates temporary table in mmddyyyy format to SQL Server dates.
-- Transform clean MMDDYYYY dates to SQL SERVER DATES SELECT Staging.[dbo].[MMDDYYYY_TO_SQL_DATE]([ASSIGN DATE]) [ASSIGN DATE] FROM #MSQLTips_MMDDYYYY_Dates
How do I handle rows with missing date strings?
The MMDDYYYY_TO_SQL_DATE function worked for the original column of ASSIGN DATE values, but it failed when applied to a second column of dates in mmddyyyy format; for our data, the second column had the name SETUP DATE. It turns out that the second column of mmddyyyy values included some rows with blank values. When the MMDDYYYY_TO_SQL_DATE function was applied to this second column of mmddyyyy string values, an error was returned. The custom function failed because there were no date parts to parse and re-combine for use with the CAST function. SQL Server does not recognize blank mmddyyyy date strings as NULL values.
Because the SETUP DATE column of values were either blank or an mmddyyyy string value, one approach was to just transform blank values to NULL SQL Server date values. Instead of modifying the initial function, our implementation was to check for blank date string values (”) and assign a NULL value for these rows. The check for a blank date string value is performed inside a CASE statement. If there is no blank value, the original MMDDYYYY_TO_SQL_DATE works validly as is. If there is a blank value, then the CASE statement assigns a NULL value instead of the return value from the CAST statement.
Without the GO statement between the two script parts, the second part of the script would never get a chance to operate after a failure in the first part because of a conversion error. The GO statement divides the two script parts into two separate batches. After the first part concludes, the second part operates whether or not there was a conversion error in the script’s first part.
-- Transform unclean MMDDYYYY dates to SQL SERVER DATES SELECT AGOStaging.[dbo].[MMDDYYYY_TO_SQL_DATE]([SETUP DATE]) [SETUP DATE] FROM #MSQLTips_MMDDYYYY_Dates GO -- Fix conversion failure for transformation of unclean MMDDYYYY dates to SQL SERVER DATES SELECT CASE WHEN [SETUP DATE] <> '' THEN AGOStaging.[dbo].[MMDDYYYY_TO_SQL_DATE]([SETUP DATE]) ELSE NULL END [SETUP DATE] FROM #MSQLTips_MMDDYYYY_Dates
The following pair of screen shots show the outcome of running the preceding script in both the Results tab and the Messages tabs of SSMS. Notice that the first result set has just one row of output (although there 100 rows of data in the SETUP DATE column). The second result set in the Results shows a NULL value for the second row and then subsequent date values after the NULL value; just the first 10 rows of the second result set are shown to conserve space. The Messages tab displays two separate and different messages for the first and second batch. The first batch results in a conversion error message. The second result set generated by the second batch indicates that 100 rows were processed. This confirms that the fix successfully handled the blank mmddyyyy date string values.
How do I compare a converted date string from one SELECT statement to another converted date string in another SELECT statement?
The final question that the business analyst asked was how to compare the dates returned by the two different SELECT statements. While is easy to merge the two SELECT statements for ASSIGN DATE and SETUP DATE into one SELECT statement, another approach is to keep the SELECT statements as originally developed and then join their result sets. One way to facilitate this is by adding an account number field to the result set from each result set and then to join the result set from each SELECT statement by account number field values.
Another challenge for the business analyst was how to handle missing or NULL SETUP DATE values when performing comparisons. In other words, he was not sure if a NULL value for a SETUP DATE was before or after a non-null ASSIGN DATE value. In developing your solutions to issues like this, you need to recall that the comparison of any non-null value to any NULL value is always NULL. That is, if either date is missing for a date comparison, then there is no valid comparison because one date value is missing.
The following T-SQL script shows one approach to implementing the guidelines described above for answering the third question. Notice the setup_dates and assign_dates sub-queries are nearly identical to the previously discussed SELECT statements for returning ASSIGN DATE and SETUP DATE date values. Each SELECT statement is modified to return an ACCOUNT# field value along with the sql date field value. Furthermore, the two sub-queries are inner joined by their ACCOUNT# field values. This join brings the ASSIGN DATE and SELECT DATE values for each ACCOUNT# into the same result set.
The top SELECT statement in the following script shows the ACCOUNT# field value for each row in the joined result set along with the corresponding ASSIGN DATE and SETUP DATE values. In addition, a CASE…END statement performs the comparison between dates for the ACCOUNT# on a row. The first WHEN clause returns the value ‘ASSIGN DATE <= SETUP DATE' when the ASSIGN DATE is before or equal to the SETUP DATE. The second WHEN clause returns the value 'ASSIGN DATE > SETUP DATE’ when the ASSIGN DATE is after the SETUP DATE. Finally, the ELSE clause returns value ‘ASSIGN DATE and/or SETUP DATE is missing’ when the condition expression for either of the preceding two WHEN clauses is not true. /p>
-- Format two different date columns for side-by-side comparison
SELECT
assign_dates.ACCOUNT#
,[ASSIGN DATE]
,[SETUP DATE]
,
CASE
WHEN [ASSIGN DATE] <= [SETUP DATE] THEN 'ASSIGN DATE <= SETUP DATE'
WHEN [ASSIGN DATE] > [SETUP DATE] THEN 'ASSIGN DATE > SETUP DATE'
ELSE 'ASSIGN DATE and/or SETUP DATE is missing'
END [comparison outcome]
FROM
(
SELECT
ACCOUNT#
,AGOStaging.[dbo].[MMDDYYYY_TO_SQL_DATE]([ASSIGN DATE]) [ASSIGN DATE]
FROM #MSQLTips_MMDDYYYY_Dates
) assign_dates
INNER JOIN
(
SELECT
ACCOUNT#
,CASE
WHEN [SETUP DATE] <> '' THEN AGOStaging.[dbo].[MMDDYYYY_TO_SQL_DATE]([SETUP DATE])
ELSE NULL
END [SETUP DATE]
FROM #MSQLTips_MMDDYYYY_Dates
) setup_dates
ON assign_dates.ACCOUNT# = setup_dates.ACCOUNT#
The following screen shot shows an excerpt from the outcome of the preceding script. There are six rows in the excerpt. The first row as well as the third through fifth rows shows comparisons where the ASSIGN DATE value is before or equal to the SETUP DATE value. The second row with the cursor resting the comparison outcome column shows the result when SETUP DATE value is NULL. The sixth and final row in the excerpt illustrates the comparison outcome column value when the SETUP DATE is before the ASSIGN DATE.
Next Steps
This tip is not about the right or most efficient way to convert an mmddyyyy date string to a SQL Server date and to compare the results to another string-to-date conversion. Instead, the focus of this tip on how SQL Server professionals are likely to encounter questions form their colleagues who need help with SQL Server. In my experience, colleagues of SQL Server professionals typically do not necessarily ask for what they really need initially. If you have the time and interest to help (or it is actually your job to help) consider implementing a solution to what they really need in several solutions that successively converge on their real business need for some T-SQL code.
Conversions of strings to date can be a tricky topic with real world data that is not in the precise format that a SQL Server function requires. For this reason, you should consider some preliminary data cleansing before passing a string value to a SQL Server conversion function. In this tip, we demonstrated this general issue with a simple missing value. However, it is probably more common to encounter non-missing values that do not convert well to dates that you would likely encounter such as 02302014 or 02280001.
If you are going to help your colleagues and/or personally deal with real world string data representing dates, it is likely that you will want to take advantage of the best that SQL Server has to offer for string-to-date conversions. Of course, MSSQLTips.com is a great resource for finding tips on converting strings to dates. Here are some URLs for tips that I found especially interesting. I encourage you to search for your own list of most useful string-to-date conversion tips. Hopefully, this article will be among them for you.
