View Issue Details

IDProjectCategoryView StatusLast Update
15199Bug reports[All Projects] Survey takingpublic2019-09-02 10:42
ReporterDenisChenu Assigned ToDenisChenu  
PrioritynoneSeverityminor 
Status closedResolutionfixed 
Product Version3.17.x 
Target VersionFixed in Version3.17.x 
Summary15199: MSSQL : date/time question are not reloaded properly
Description

When using MSSQL : date time question broken when reload survey (via resume later option)

Steps To Reproduce
  1. Import included survey in a MSQL system
  2. Start survey, next
  3. Enter 2 dates and something
  4. Move next
  5. Resume later
  6. Launch survey with newtest=Y
  7. Move previous
  8. Check date :
    8.a : date input is empty : broken
    8.b : date dropdown seems OK
    9 Move next
  9. Move previous
  10. Date dropdown is empty
    12 : filling date (input and dropdopwn) is OK when not reload …
Additional Information

IN DB datetime are saved as 1909-09-14 00:00:00.000

Surely need to remove the extra .000 …

TagsNo tags attached.
Complete LimeSurvey version number (& build)3.17.13
I will donate to the project if issue is resolvedNo
Browserfirefox
Database & DB-VersionSQLServer
Server OS (if known)windowserver 2016
Webserver software & version (if known)IIS
PHP Version7.2.16

Activities

DenisChenu

DenisChenu

2019-08-27 18:43

developer  

survey_archive_dateIssue.lsa (3,839 bytes)
jelo

jelo

2019-08-28 11:02

partner   ~53321

Abstracting the whole datetime questions to hide the db differences are badly needed. The NULL vs empty issue (answering behavior) is demanding a change too.

I'm not using MS SQL with LimeSurvey, but when you state you need to remove the extra .000, I wonder what datatype is expected?

The recommendation for "most" compatible to SQL Standard is to use only
time, date, datetime2 and datetimeoffset data types

datetime
2007-05-08 12:35:29.123

smalldatetime
2007-05-08 12:35:00

datetime2
2007-05-08 12:35:29. 1234567

datetimeoffset
2007-05-08 12:35:29.1234567 +12:15

Changing types via CAST will result in different times.
SELECT CAST('2019-08-28 12:59:59.998' AS smalldatetime);
2019-08-28 12:59:59.998 -> 2019-08-28 13:00:00

The most common mapping between MySQL and SQLServer seems to be this:
DATETIME - datetime2
DATE - date
TIME - time2
TIMESTAMP - smalldatetime

DenisChenu

DenisChenu

2019-08-28 11:12

developer   ~53322

smalldatetime , yes maybe we can set this in mssql …
I try

About null VS empty : mysql accept 0000-00-00 00:00:00 for date time (if you set as ""), but not mssql.
Then : null VS empty is DB part too …

DenisChenu

DenisChenu

2019-08-28 11:34

developer   ~53323

Can you quickly check if you same issue on pgsql ?
Seem pgsql use timestamp : 1 microsecond / 14 digits
https://www.postgresql.org/docs/9.1/datatype-datetime.html

Then i think you can have same issue (if not … it's something other …)

jelo

jelo

2019-08-28 12:36

partner   ~53325

Sorry, I'm currently not running a Postgres test environment.

SQL -> PostGres
DATETIME -> TIMESTAMP(3)
DATETIME2(p) -> TIMESTAMP(p)
DATETIMEOFFSET(p) -> TIMESTAMP(p) with TimeZone

Correct, MySQL allows to store '0000-00-00' ... to data types related to dates.
MS SQL and Postgres both don't.

You sometimes see conversion tools adding NULL for these fields.

The masterplan for getting MySQL, SQLServer and PSQL abstracted so that the questions types can be coded without DB exceptions is overdue.

DenisChenu

DenisChenu

2019-08-28 13:18

developer   ~53326

Last edited: 2019-08-28 13:23

View 2 revisions

@jelo : the NULL vs empty value already have their feature if i remind … this is not related to this issue

Checkjed with pgsql : OK for YY-M-D H:i:s like mysql : timestamp(0)

Isue only for mssql

DenisChenu

DenisChenu

2019-08-28 13:51

developer   ~53327

https://github.com/LimeSurvey/LimeSurvey/commit/c3ab1051f02e5a2c2de551c05b55d3aee09ae66f

lime_release_bot

lime_release_bot

2019-09-02 10:42

developer   ~53376

Fixed in Release 3.17.14+190902

Issue History

Date Modified Username Field Change
2019-08-27 18:43 DenisChenu New Issue
2019-08-27 18:43 DenisChenu Status new => assigned
2019-08-27 18:43 DenisChenu Assigned To => DenisChenu
2019-08-27 18:43 DenisChenu File Added: survey_archive_dateIssue.lsa
2019-08-28 11:02 jelo Note Added: 53321
2019-08-28 11:12 DenisChenu Note Added: 53322
2019-08-28 11:34 DenisChenu Assigned To DenisChenu => eddylackmann
2019-08-28 11:34 DenisChenu Status assigned => feedback
2019-08-28 11:34 DenisChenu Note Added: 53323
2019-08-28 11:35 DenisChenu Assigned To eddylackmann => DenisChenu
2019-08-28 11:35 DenisChenu Status feedback => assigned
2019-08-28 12:36 jelo Note Added: 53325
2019-08-28 13:18 DenisChenu Note Added: 53326
2019-08-28 13:23 DenisChenu Note Edited: 53326 View Revisions
2019-08-28 13:51 DenisChenu Status assigned => resolved
2019-08-28 13:51 DenisChenu Resolution open => fixed
2019-08-28 13:51 DenisChenu Fixed in Version => 3.17.x
2019-08-28 13:51 DenisChenu Note Added: 53327
2019-09-02 10:42 lime_release_bot Note Added: 53376
2019-09-02 10:42 lime_release_bot Status resolved => closed