Datetime issue - Import data from SQL Server

[harvey_nguyen]

I am using neo4j-admin import tool to import data from large csv files. These files are exported from SQL Server.
The problem that I am facing is different format between SQL and Neo4j:

In SQL Server, datetime field is: 2018-08-16 01:15:34.500
When I import data, it has an error: Text cannot be parsed to a DateTime

Because using neo4j import tool, I cannot use any apoc or something like that to reformat this string
How can I solve this ? (I am using Neo4j community 3.4.7)
Thanks in advance.

[ryan_boyd · Accepted solution]

Hi Harvey,

Ah, it's probably an issue with the milliseconds there.

Suggest importing as a string into a temp property and then using Cypher to update all the nodes and set the real date-time property.

Cheers,
-Ryan

[harvey_nguyen]

Hi Ryan,
Thanks for your reply,

So I have to use some tricks in this case. However, I think Neo4j should be enhanced to handle Datetime well in next versions.

Thanks.
Harvey

[ryan_boyd]

Agreed that it’d be good if we could specify a datetime format string along with the type.

Will put in that request.

Cheers
Ryan

[benjamin_squire]

I am having the same issue. Lets Can we improve datetime Function for Epoch timestamps as well?

[yudiellhernande]

The issue seems to be the space between the date and the time. Replacing the space for "T" fixed the issue for me.
MATCH(n:Node) RETURN datetime( REPLACE(n.DateTime," ","T"))

[julian_n_de_jon]

Thanks, found this really helpful. Not sure if anyone else had the same issue, but I found replacing the space with a "T" imports this column as a string, not a datetime type. Therefore it still needs to be converted into a datetime series after.

[ameyasoft]

Try this:

with "2018-08-16 01:15:34.500" as dte
RETURN apoc.date.format(apoc.date.parse(dte, 'ms', 'yyyy-MM-dd'), 'ms', 'yyyy-MM-dd')

result:
"2018-08-16"

During import from csv file replace 'dte' with date column say row.date.

[senthilc78]

Faced same issue but below code works for me without apoc .

1. SPLIT milliseconds separate
2. Replace space btw Date and time with "T"

Eg.
LOAD CSV WITH HEADERS FROM "file:///sourcepath.csv" AS csvRecord

RETURN datetime(REPLACE(SPLIT(csvRecord.dt,".")[0]," ","T"))

CSV date field dt contains "2020-01-25 16:18:24.000"

Result : "2020-01-25T 16:18:24Z" .

Hope it helps!