1

I need to restructure the R POSIXlt object into an appropriate T-SQL query that is going to return a SQL datetime. I imagine there should be a way to conver the POSIXlt to a double and then use that double to make a SQL datetime, but I am not able to figure out how exactly to do that. Here is what I have tried:

date = as.POSIXlt(Sys.time())
date_num = as.double(date)

After that I copied the contents of date_num (1469491570) and attempted to paste it into the following SQL query to see what happens:

select CONVERT(datetime, convert(varchar(10), 1469491570));

However, this errors out, which I guess is expected, since it expects a string representation of a date not some random number...

EDIT: I am looking to find something that can convert the POSIXlt to a number of tics or milliseconds from, say, 1900 and then uses that number to create a T-SQL datetime.

doubleOK
  • 353
  • 6
  • 19
  • the function `julian(x, origin = as.Date("1970-01-01"), ...)` can convert the POSIX object to the number of days to your specified origin then you should be able to convert to the number of seconds or milliseconds. – Dave2e Jul 26 '16 at 01:10
  • @Dave2e that actually is exactly what I wanted. You should post it as an answer. – doubleOK Jul 26 '16 at 22:19

2 Answers2

0

It's a bit unclear because you keep talking about "dates" but appear to want times (duration in small intervals since a reference date). Try this:

      date = as.POSIXlt(Sys.time())
      date_num = as.numeric( as.POSIXct(date) ) # should now be seconds from MN 1970-01-01

Noting further that Sys.time() would have been POSIXct-class. According to this there is a TSQL datetime with a format exactly as you would see from format.POSIXt using the default values for the 'format' argument: https://msdn.microsoft.com/en-us/library/ms186724.aspx, so you might even try (with suitable class definition of the receiving columns:

date = format( as.POSIXlt(Sys.time()) )
date     # now a character value
#[1] "2016-07-25 18:48:08"
IRTFM
  • 258,963
  • 21
  • 364
  • 487
0

The function: 

julian(x, origin = as.Date("1970-01-01"), ...)

can convert the POSIX object to the number of days to your specified origin then you should be able to convert to the number of seconds or milliseconds.

For example:

as.numeric(julian(as.Date("2016-07-25")))

is 17007 days from Jan 1, 1970 (Beginning of time for Unix) or 

as.numeric(julian(as.Date("2016-07-25"), origin = as.Date("1900-01-01")))

or 42574 days from close to the beginning of time for windows.

Dave2e
  • 22,192
  • 18
  • 42
  • 50