Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

SPSS Export SDATE19 Date Problem

  • petert
  • petert's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 6 months ago - 10 years 6 months ago #99556 by petert
SPSS Export SDATE19 Date Problem was created by petert
I have a bunch of surveys. They are all set to automatically contain their submitdate, startdate and datestamp. All of these values correctly arrive in SPSS as expected.

However, all dates which I have as part of the actual survey fail to import into SPSS. Looking at the .sps and .csv files, it's clear that the difference between the dates which work and those that don't is their variable type declaration in the .sps file, and the time format in the .csv file.

So, the auto dates are variable type:

V3 DATETIME23.2
...
V6 DATETIME23.2
V7 DATETIME23.2
...
V11 SDATE19.

The SDATE19 variables fail to import correctly. In the .csv file the DATETIME23.2 look like this:
'02-09-2013 10:41:45'

While the SDATE19 look like this:
'2013-09-29 00:00:00'

Now that looks like a proper date, but SPSS chokes every time on it. I just want to change the SPSS export do all dates like DATETIME23.2, and not change to SDATE when it comes to user entered data.

The *really* confusing part, is that the database shows them as exactly the same data types. (See attachments). So why does the export engine switch from DATETIME23.2 to SDATE19 when the data type and data itself is identical.

I have experimented with altering the date format at both the question and survey levels with no change in the SPSS export
behavior.

Thoughts appreciated.



Last edit: 10 years 6 months ago by petert. Reason: .dat --> .csv
The topic has been locked.
  • mdekker
  • mdekker's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
10 years 6 months ago #99573 by mdekker
Replied by mdekker on topic SPSS Export SDATE19 Date Problem
Can you please file a bug report for this and include a simple survey demonstrating the problem?

The sdate format is a sortable date (yyyy-mm-dd) but apparently this can not hold time. Not really sure why this is a problem now, could be related to spss version. The datetime format is dd-mm-yyyy hh:mm:ss so you can not just change the variable declaration, also the csv data should be reformatted.

---
Menno Dekker
The topic has been locked.
  • petert
  • petert's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 6 months ago - 10 years 6 months ago #99578 by petert
Replied by petert on topic SPSS Export SDATE19 Date Problem
Well, at least I found why certain field types are handled differently.

application/helpers/export_helper.php line 415-416 says:
Code:
if ($fieldname=='submitdate' || $fieldname=='startdate' || $fieldname == 'datestamp') {
            $fieldtype = 'DATETIME23.2';

By adding my own column names to the line like this:
Code:
if ($fieldname=='submitdate' || $fieldname=='startdate' || $fieldname == 'datestamp' || $fieldname == '617976X218X3433') {

I was able to get a correct export dump.

Looking at line 340 I find:
Code:
'D'=>Array('name'=>'Date','size'=>10,'SPSStype'=>'SDATE'),

So, that's where the action is happening, and explains the inconsistency.
Last edit: 10 years 6 months ago by petert. Reason: bad code flag
The topic has been locked.
  • petert
  • petert's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
10 years 6 months ago #99580 by petert
Replied by petert on topic SPSS Export SDATE19 Date Problem
Quick hack fix: Change line 340 in applications/helpers/export_helper.php to:

Old:
Code:
'D'=>Array('name'=>'Date','size'=>10,'SPSStype'=>'SDATE'),

New:
Code:
'D'=>Array('name'=>'Date','size'=>10,'SPSStype'=>'DATETIME23.2'),

Result in all dates being exported as DATETIME23.2.
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose