Trying to solve the SQL SERVER extract to MS access Date time import Conundrum? (1 Viewer)

[sportsguy]

SQLSERVER text extract for datetime has a text format with milliseconds.
'2015-07-13 15:20:25.0000'

MS Access link table format can't handle more than 19 characters, or the period and anything to the right of the period.

Exporting the SQLSERVER date time as LEFT(CREATED_DATETIME,19) as text
results in 'Jul 13 2015 3:16PM'

So the question is, how to create a table input mask to turn 'Jul 13 2015 3:16PM' text into MS Access datetime?

Anyone ever try this before?

Thanks, sportsguy

 

[spikepl]

I'm confused. What has an input mask to do with anything? The function Cdate gobbles up a string containing datetime and spits out a date variable you can stuff into an access table.

But I'm surprised that text enters into this at all. Did you try to link an Access date field to your SQL field?

 

[sportsguy]

its much easier to extract a text file of 10 million rows and import them than to have ODBC append 10 million rows of data.

But I solved the problem. I edit and replaced the .000 at the end of the SQLSERVER time field in the text file, and then MA Access linked view sees the data as datetime!

then can read the file in extremely efficiently



sportsguy

 

[The_Doc_Man]

I have a similar problem when importing times from my system that has time audit logs to hundredths of a second. After playing with this for a while, I came up with two ways to handle this.

First, if the times span more than a day or so, who CARES about milliseconds - so I edited the import data to remove the decimal point and fractional seconds.

Second, where I was looking at a relatively short time frame and wanted to keep the times in order, I import the time as text into a temp table. I split the string to a date/time field and the fractional field by separating the text at that decimal point. Then I do a CDBL(date/time field) and a CDBL( 0.nnnn / 86400 ) I.e. convert the fractions of a second to fractions of a day. Add the DOUBLE values together and sort by the DOUBLE value. Then the date/time (with fraction string) displays in the correct order to the fraction. This gets cumbersome if you ALSO need the date/time to be used as a native date/time value as well, because you need another field for that.

The second method works because dates are actually just a number days and fractions of a day, in DOUBLE format internally, since the reference date. That number is always > 0 so you can get away with splitting the parts and re-adding them in a different format.