Convert Nvarchar Datatype to Datetime-CodePudding

I am practicing on a database on SQL server where the date column in the table is Nvarchar(255) thereby presenting the dates as five digit numbers eg 40542,40046 etc. How do I change the dates in the column to actual dates?

The articles I checked only helped me to change rows but I can't change all of them individually as that will take time.

CodePudding user response：

If dates from Excel, this is to expand on my comment.

Here are two approaches

Example

Declare @YourTable Table ([SomeCol] nvarchar(255))  Insert Into @YourTable Values 
 ('40542')
,('40043')
 
Select *
      ,AsDate1 = try_convert(datetime,try_convert(float,SomeCol)-2.0)
      ,AsDate2 = dateadd(day,try_convert(int,SomeCol),'1899-12-30')  -- Note Start Date
 From @YourTable

Results

SomeCol AsDate1     AsDate2
40542   2010-12-30  2010-12-30
40043   2009-08-18  2009-08-18

CodePudding user response：

You have a strange database. Basically, you want

UPDATE TableName
  SET NewDateColName = ConvertionFunction(OldDateColName)

But more info is needed on how to convert the 5 digit number encoded as a string, into a date.

CAST(OldDateColName as Date)

Might work, or you may have to apply some arithmetics after casting to int.

For excel dates:

UPDATE TableName
  SET NewDateColName = CONVERT(numeric(18,4),OldDateColName,101)

CodePudding user response：

The values of your "dates" suggests that your dates reflect an offset in days since the epoch (zero point on the calendar system) of 1 January 1900.

For instance, the values you cited are:

40452: Sunday, October 3, 2010
40046: Sunday, August 23, 2009

In which case you should be able to say something like

create view dbo.my_improved_view
as
select t.* ,
       some_date = date_add( day,
                             convert( int  , t.funky_date_column ),
                             convert( date , '1900-01-01'        )
                           )
from dbo.table_with_funky_date_column t