CAST AS DATE Returns in NVARCHAR Format

Question

CAST(GETDATE() AS DATE) returns in NVARCHAR(10). DATETIME changes the column to a date, but I don't want the time in it. I want a DATE formatted column that just contains the date. What am I missing?

Answer 1

As a way to demonstrate that cast(getdate() as date) does return the data type of date

select
   cast(getdate() as date) cast_getdate
into MyTable_x
;

select
  TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE 
from information_schema.columns
where table_name = 'MyTable_x'
;


+------------+--------------+------------------+-----------+
| TABLE_NAME | COLUMN_NAME  | ORDINAL_POSITION | DATA_TYPE |
+------------+--------------+------------------+-----------+
| MyTable_x  | cast_getdate |                1 | date      |
+------------+--------------+------------------+-----------+

Answer 2

I'm guessing you need to change the datatype of the column. Try this:

ALTER TABLE [Table Name] ALTER COLUMN [Column Name] DATE

Answer 3

So apparently it is storing the value with date, it just shows the wrong type on the returned results. If you can see the image, It shows it as Date in two other places, but in the bottom section you can see where it shows as NVARCHAR. I'm not sure why it does that, probably something to do with SQL DBX. Either way, sorry for wasting everyone's time. I've just never seen it display the wrong type in the returned results.