Inbuild datatype of column

To determine the inbuilt datatype of the column MonthYear when you format GETDATE() to 'MMM-yy', you can use the SQL_VARIANT_PROPERTY function in SQL Server. This function returns the base datatype of a SQL Server expression.

 

Query to Find the Datatype

SELECT 
    SQL_VARIANT_PROPERTY(FORMAT(GETDATE(), 'MMM-yy'), 'BaseType') AS DataType;

 

1. FORMAT(GETDATE(), 'MMM-yy'):

   • Formats the current date to a string in the format MMM-yy (e.g., Oct-23).

   • The FORMAT function always returns a string (NVARCHAR).

2. SQL_VARIANT_PROPERTY:

   • Returns metadata about a SQL Server expression.

   • The 'BaseType' property returns the base datatype of the expression.

3. Result:

   • The result will be NVARCHAR, as the FORMAT function returns a string.

 

Example Output

DataType
NVARCHAR

 

Why is it NVARCHAR?

• The FORMAT function in SQL Server always returns a Unicode string (NVARCHAR), regardless of the input.

• The length of the output depends on the format string. For 'MMM-yy', the length is 6 characters (e.g., Oct-23).

 

• If you want to explicitly define the datatype of the MonthYear column in a table, you can use NVARCHAR(6) or VARCHAR(6) (if Unicode is not required).

• Example:

  CREATE TABLE ExampleTable (
      MonthYear NVARCHAR(6)
  );

• The FORMAT function returns a string (NVARCHAR).

• Use SQL_VARIANT_PROPERTY to determine the datatype of any SQL Server expression.

• For 'MMM-yy', the datatype is NVARCHAR(6).