Python and SQL Server: Issue with datetime Data

Question

I have a SQL Server table that contains some datetime data. I'm using a Python script with pymssql to connect to the database and execute queries.

The problem I'm having is that when my queries return datetime data, the dates are shifted and are no longer accurate. For example, one data entry in my table is for the date '2012-03-27', but when I retrieve it and store it in my script as a datetime object in Python, the date is stored as '2012-01-03'.

Any ideas on how to fix this? Should I be using a library like pytz?

Thanks!

This seems incredibly strange. There's no way something 'converting' the 27th March to the 3rd January could be a timezone issue. Not as far as I can see. Could you paste the related code section from where you get the date to where you store it? — David Downes
– David Downes, Commented Apr 10, 2012 at 12:57
you're obviously looking at the wrong data. provide the code. — WeaselFox
– WeaselFox, Commented Apr 10, 2012 at 13:01
Unless something out of my head is happening (which often does though!), this should not happen. But things happens. Have you checked your code thoroughly. — Jack_of_All_Trades
– Jack_of_All_Trades, Commented Apr 10, 2012 at 13:01
Please post your code! I am thinking about your problem for a long time now assuming it as an interesting case and finally hit a dead end! As of curiosity, I want to see you code...desperately. — Jack_of_All_Trades
– Jack_of_All_Trades, Commented Apr 10, 2012 at 13:14
This looks like a month <-> day switch that is passing errors silently. 3/27 somewhere is becoming 27/3 then 27 too large for month is becoming 1 (I.e. null). My guess is somewhere under the hood a conversion to string is happening to pass into SQL. — Corvus
– Corvus, Commented Apr 8, 2016 at 16:57

cfragni · Accepted Answer · 2013-10-01 14:30:00Z

1

I am having the same issue with pymssql version 1.02. Although its not the cleanest aproach, I am using the workaround suggested by the OP and converting the string value to a python datetime object with the correct values using the dateutil.parser module. Here is the code I am using, provided that iso_datetime_string contains the string with the datetime in ISO format, as returned by the workaround:

import dateutil.parser as ps

correct_datetime_obj = ps.parse(iso_datetime_string)

answered Oct 1, 2013 at 14:30

cfragni

463 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

spookymodem · Accepted Answer · 2012-04-12 13:29:57Z

0

I'm still not sure what is causing this problem, but I found a solution: convert the datetime data to type varchar when the query is performed:

select convert(varchar(20),event_time,120) from event_detail;

Previously I was performing this query:

select event_time from event_detail;

where event_time is of type datetime

answered Apr 12, 2012 at 13:29

spookymodem

5311 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

Python and SQL Server: Issue with datetime Data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related