0

I am fairly new to C# and Microsoft SQL Server but I wrote a code in Visual Studio using C# that will read in a .csv file, parse the data and place the correct values in the correct columns of a Microsoft SQL Server table.

The issue I am having is that the dates being sent to me are epoch dates. I know that my number is counting the seconds from 12/31/1899 11:59pm. Right now, I have it just being inputted into my table as a number.

For example: 3673900800. I was wondering if there was a way to change my table definition in my table in order for it to automatically convert that number into a date.

For example, setting the data type as a smalldatetime. I did try this, but it didn't work. Do I have to convert the number in my code. before sending it to the SQL table, or is there a way to have the server automatically convert it

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user6451557
  • 29
  • 5
  • can you post the exact code that you are using to convert that date into an Integer..? also have you done a google search on the following `C# stackoverflow Convert DateTime String into Epoch time` here is a link to help you get started http://stackoverflow.com/questions/9453101/how-do-i-get-epoch-time-in-c – MethodMan Jun 13 '16 at 16:17
  • I am not converting the date into an integer. It is sent as an integer in the files I am receiving – user6451557 Jun 13 '16 at 16:20
  • then do the same thing convert the integer into a Timestamp I am sure there are examples of Converting Integer into TimeStamp as well please try a little harder in your research / google searches.. – MethodMan Jun 13 '16 at 16:22
  • 1
    Are you sure it is using 1899-12-31 23:59 as the start? That is pretty strange. The Unix Epoch date is the number of seconds since 1970-01-01. – Sean Lange Jun 13 '16 at 16:39
  • I did research. That wasn't my question. My question was if it can automatically convert it for me in my SQL table if i gave it a specific datatype and default value or if the only option was to convert it manually before inserting it in my table – user6451557 Jun 13 '16 at 16:40
  • @SeanLange Yes I am sure that is the start. I am having a report sent to me. The system in which the report is formulated converts the date to an integer. I researched the system and it stated that as the time start date. However, now that the report is sent to me, I need to insert it into my own table, except that I do not want it as an integer, I want it back as a date. – user6451557 Jun 13 '16 at 16:43

2 Answers2

0

You can use datetime field and insert your numbers as 3673900800 / 60 / 60 / 24 (2016-06-03).

Example

declare @tmp table(id int identity(1,1), dt datetime, epoch bigint)
insert @tmp(dt,epoch)
values (3673900800/ 60 /60/24,3673900800)

select *,cast(dt as float)*60*60*24 [restore] from @tmp
Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36
  • I get this error when I change the field to datetime: Error Converting '3674419200' to type: 'DateTime'. There are more chars in the Input String than in the Format string: 'ddMMyyyy' – user6451557 Jun 13 '16 at 17:04
0

Some simple date math is all you need here.

select dateadd(minute, 3673900800/60.0, '1899-12-31 23:59')

An easy way to handle this would be with a computed column. Here is an example.

create table #Something
(
    EpochDate bigint
    , DateTimeValue AS dateadd(minute, EpochDate/60.0, '1899-12-31 23:59')
)

insert #Something
select 3673900800

select *
from #Something

drop table #Something
Sean Lange
  • 33,028
  • 3
  • 25
  • 40