Inbuild datatype of column

Question

How to find what will be the inbuild datatype of this column - MonthYear once we format Getdate() to 'MMM-yy' select FORMAT(getdate() 'MMM-yy') AS MonthYear

Tuhin Paul · Answer

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; 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 ). SQL_VARIANT_PROPERTY : Returns metadata about a SQL Server expression. The 'BaseType' property returns the base datatype of the expression. 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) .

Jignesh Kumar · Answer

It will be a is VARCHAR(5).