Printing the data type of a temporary variable and displaying it in either the Results or Messages t-CodePudding

In Python, if I convert a variable, I can check that the variable is the intended data type via a try-catch block, or just print to the console after the conversion takes place.

How can I do something similar in SQL server 2019?

declare
@Rep_Date VARCHAR(64) = '2023-11-02';

Below is implicit conversion. Is there a better explicit conversion available to use?
If so, how would I do this?

declare
@Per_Date DATETIME = @Rep_Date;

There's code "below" this that never uses @Rep_Date only uses @Per_Date

How can I verify/be check that @Per_Date is a DATETIME temp variable and not VARCHAR(64)? */

To print the data type to "console" I have tried:

print(type(@Per_Date))
print(type(select @Per_Date))

To explicitly convert I have tried:

I have only used CAST or CONVERT in a select statement but never directly on a single temp variable.

CodePudding user response：

If you want explicit conversion then you would use CONVERT or CAST or, if you want to avoid conversion errors, TRY_CONVERT or TRY_CAST (which will return NULL instead when the conversion fails).

As for getting the data type you could (ab)use the SQL_VARIANT_PROPERTY function. If you pass the variable to it as the first parameter, it will be implicitly converted to a sql_variant of the base type of the variable, and then you can request to base type from the function:

DECLARE @Rep_Date varchar(64) = '2023-11-02', 
        @Act_Date date;
SET @Act_Date = CONVERT(date,@Rep_Date,120); --Style 120 is yyyy-MM-dd
SELECT @Rep_Date AS Rep_Date,
       @Act_Date AS Act_Date,
       SQL_VARIANT_PROPERTY(@Rep_Date,N'BaseType') AS Rep_Date_Type,
       SQL_VARIANT_PROPERTY(@Act_Date,N'BaseType') AS Act_Date_Type;

As you'll note, however, this doesn't tell you what the length/precision/scale/Bytes of that data type is. You would need to use 'MaxLength'/'Precision'/'Scale'/'Bytes' to get that.

CodePudding user response：

You do this with Python because it is dynamically typed.

SQL is not dynamically typed. In SQL, a variable is and always will be exactly the type you declare it to be. If an implicit conversion fails with an assignment, the code immediately throws an error.

This means there is no possible way for the code in the question to end up with @Per_Date as a VARCHAR(64). It can't happen.

The one thing you might want is TRY_CONVERT() or TRY_CAST(), which allow you to attempt a conversion without throwing an error.