Schema Compare is unaware of DATETIME2 precision

SQL Assistant 13.0.56 Professional Edition

I frequently use DATETIME2(0) to reduce the number of bytes consumed and because I don't need precision more than 1 second.
If I need more precision I use DATETIME2(2) to get to the 1/100th of a second and still save a byte compared to the legacy DATETIME data type.

However, SQL Assistant 13's Schema Compare appears to be unaware of the precision of the DATETIME2 data type.

Is there a setting I'm missing somewhere, or is this a bug/deficiency of SQL Assistant 13.0.56 Professional Edition

I'm unable to understand from your description what kind of issue you are facing. A screenshot would be very helpful as well as mentioning types of databases compared and their specific versions as that might be important too.

And yes, most things can be customized in the Schema Compare tool, but first we need to understand what exactly isn't working for you.

My guess is that he complains about not removing the zeros for the fraction seconds when using datetime2(0), which will end in result sets like this (left column):

You could do that by modifying/changing the display formats:

and picking Hide zero fraction

However, that's still very far from being perfect (you have to do that for every column manually), and the Format input (by default) only allows formatting hours/minutes/seconds and has no settings (that I know of, but I should check the user guide) for fractional seconds, meaning that unless you change the Display Format, the SA result pane will display times with 3 decimal points of fraction seconds regardless of the result being that precise or not. It also only shows those 3 fraction decimals when the datetime2 is defined to be more precise than that (right column, with datetime2(6).

And I think that discrepancy makes two, otherwise equal values to be considered unequal (and/or vice verse) resulting in false negatives/positives in the comparison.

I think you are referring to what would be relevant to the Data Comparison feature, while the original post is referring to Schema Comparison...

Yes, I misread that. But what I want to point out is that SA/SE does already (seemingly) disregard (in some cases) that some data types might have precision/length/width/whatever, so the root cause might be common.

gemisigo

	Quote:
	Yes, I misread that. But what I want to point out is that SA/SE does already (seemingly) disregard (in some cases) that some data types might have precision/length/width/whatever, so the root cause might be common.

This. Sorry for lack of detail. I'm referring to the Schema Compare (using SQL Server)

The generated code is unaware that I chose "0" as the precision of my datetime2().

Likewise if one column was datetime2(0) and the other was datetime2(7), Schema Compare would NOT detect a difference.

So now it's clear, you would like to show/compare datetime2 scales. Here is how you can have it

In SQL Assistant Options navigate to "Schema Compare" tab
In the left top list select "SQL Server"
In the list below find and select "Columns (min version 13)", note this list item name not Columns for older versions
On the right side, in the Query section below case for decimal and number, add the following

	Code:
	WHEN t.[name] IN ('datetime2', 'datetimeoffset', 'time') AND c.scale <> 7 THEN '(' + CONVERT(varchar, c.scale) + ')'

Apply changes, restart your development tool, whichever you use. After that, scale of datetime2 and time data types is going to be compared too