-2

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.

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Isioma Ikechukwu
  • 1
  • Sounds like you want an `UPDATE` statement? There are plenty of tutorials out there. You would need another column of `DATETIME2` datatype to store them in. But anyway, please do explain what date `40542` should be? – Dale K Dec 01 '22 at 00:29
  • 1
    Just curious. Does 40542 represent a date from Excel ? Like perhaps 12/30/2010 ? – John Cappelletti Dec 01 '22 at 00:39
  • Just for fun try Select convert(datetime,40542-2.0) Notice the minus 2.0. This explains the need for the adjustment https://stackoverflow.com/questions/3963617/why-is-1899-12-30-the-zero-date-in-access-sql-server-instead-of-12-31 – John Cappelletti Dec 01 '22 at 00:45
  • I would highly recommend taking the tour and learning how to use this site. – Dale K Dec 01 '22 at 01:36

3 Answers3

1

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

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)
Gaël James
  • 157
  • 13
0

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
Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135