Converting date values using a query

When data is taken in to a virtual table, if you specify the date type, the data is converted to the YYYY-MM-DD HH:MI:SS format for storage. Therefore, when conducting a search using the date field, if the date format of the source data differs from that of the virtual table, the search is not performed correctly.

 

Example) The $INAME (date) data contains data in the YYYY/MM/DD format.

　SELECT [col_date] FROM [table1] WHERE [col_date] = $INAME (date)

If the date formats differ as above, use the $VDATE function.

The $VDATE function converts the field value specified for the argument to the YYYY-MM-DD HH:MI:SS format.

　SELECT [col_date] FROM [table1] WHERE [col_date] = $VDATE($INAME(date))

Note

  • When using the $VDATE function, use $INAME, $INUM, $DSTR, $DNUM, $MINUM, $VAR to specify the argument.

  • If all the following conditions are satisfied, you must register the YYYY-MM-DD HH:MI:SS format in Input formats for dates in the Options in the Data Processing Settings screen:

    • You want to specify a date format for reading

    • You want to import data from a date-type column in a virtual table

    For details, see Reading date data when input formats for dates are specified.