Unit test in SQL Server Data Tools has Scalar Value test condition. This test condition receives a dataset (result set) from the query. You can choose the cell from that dataset with Row number and Column number properties.
If you want to check a value of variable you should use the following command:
|
1 |
SELECT @variable; |
This will return a dataset that contain only one cell, which means it has one row and one column. If you want to check a value from another cell, adjust the properties mentioned above.
I was wondering how SSDT understand the value that we set in the Expected value property. When there’s a comparison between numbers or strings, I don’t even think about conversion problems.
But what will be if we compare dates? I have not found a calendar or other tools to set datetime value. The Properties pane has a mere textbox for the Expected value. It seems that SSDT gets a string.
So I decided to conduct an experiment to check how conversion really works. I created a very simple test case. I use ISO format in cast to avoid any conversion problem.
SQL Server has a number of string format that can be used as a representation of a datetime. Full list is available in MSDN.
When I use ISO or unseparated format, the test passes. When I set the value as ‘8/15/2014 8:20:15’, it works too. But when I set the date as ‘15.08.2014 8:20:15’, the test fails.
That’s because SSDT (Visual Studio) or SQL Server has another locale or dateformat settings than the format dd.mm.yyyy.
This experiment proves that SSDT really performs conversion from string to datetime type. To avoid conversion problems always set the value in the Expected value property in ISO 8601 format:
YYYY-MM-DDThh:mm:ss[.mmm]