CODEWITHSUNDEEP
phpmysqlsqldatetime
4 Leave Cover
4 Leave Cover

Reputation: 1276

Query return NULL when using STR_TO_DATE

i have two questions, using below query:

  1. this query is for select list purpose. I want to display period1 as the text while period2 as the option value. Is this a good practice? Any vulnerability where user can exploit the select list?

  2. Why the query return NULL for period2?

    select distinct 
       STR_TO_DATE(period, '%Y-%m-%d') as period1,
       STR_TO_DATE(period, '%Y%m%d') as period2 
from table

Upvotes: 0

Views: 651

Answers (1)

Stephan Lechner
Stephan Lechner

Reputation: 35154

The reason for getting NULL lies in the definition of STR_TO_DATE:

If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning. The server scans str attempting to match format to it.

Given that definiton, it is clear that the same period-string as input cannot match two different formats; hence, one of the two STR_TO_DATE-calls will give an invalid date, i.e. NULL.

I suppose, however, that period is actually a DATE (or DATETIME) datatype, and you mean DATE_FORMAT, not STR_TO_DATE, right? Assuming this, the following query should work:

select DATE_FORMAT(period, '%Y-%m-%d') as period1,DATE_FORMAT(period, '%Y%m%d') as period2  from test

When using this scheme...

create table test (
  period date
);
insert into test (period) values ("2016-12-01");

it yields the following result:

period1    | period2
2016-12-01 | 20161201

Upvotes: 1

Related Questions