How to select multiple dates with SQL

Question

I Need to select Data from Tables of a specific user at certain days. I am not exactly sure what is wrong with my code.

Here is my code:

select k.info, z.mes_user, z.mes_date, m.val_user, m.val_date
from table_K k
left join table_M m on k.key1 = m.key1
left join table_Z z on m.key1 = z.key1 and m.key2 = z.key2
where (m.val_user like '%Username%' or z.mes_user like '%User-ID%') and
(
z.mes_date like '%18.04.17%' or
z.mes_date like '%20.04.17%' or
m.val_date like '%18.04.17%' or
m.val_date like '%20.04.17%');

The mistake is propably in the where clause.

'Username' and 'User-ID' are just placeholders for the code. The data type of mes_date and val_date are date.

create a variable of Username,User_id and date and used '=' operator — The beginner, Sep 14 '17 at 07:02
When LEFT JOIN, move the right side table conditions from WHERE to ON to get true LEFT JOIN result. (As it is now you get regular INNER JOIN result.) — jarlh, Sep 14 '17 at 07:04
What is data type of `table_M.mes_date` and `table_Z.val_date` columns? — Serg, Sep 14 '17 at 07:04
We don't know what's wrong either, because you have not shared that information. What output do you get, what do you get, do you have sample data? — HoneyBadger, Sep 14 '17 at 07:05
Don't store dates/timestamps in char/varchar columns. Use proper data types! — jarlh, Sep 14 '17 at 07:05
The dbms is Oracle. The data type of mes_date and val_date are date. Currently it Shows me the values of k.info of every user of the 2 given days. Sorry I cant provide sample date. — wenzel267, Sep 14 '17 at 07:09
Add some sample table data and the expected result. As formatted text, not images. — jarlh, Sep 14 '17 at 07:10
I only want the values of k.info of the one user i type in into %Username% and %User-ID% on the 2 given dates. Not the info of every user in the Database on these 2 days. Hopefully its a bit clearer now — wenzel267, Sep 14 '17 at 07:38

Supun Amarasinghe · Answer 1 · 2017-09-14T07:27:45.167

3

If the 'mes_date' column's type is DATE, you can do the following.

First see how the format of the date is stored in the database table. You can do it by

SELECT TO_CHAR(mes_date) FROM table_Z;

Then you can create your LIKE condition based on the format.

SELECT k.info, z.mes_user, z.mes_date, m.val_user, m.val_date
FROM table_K k
LEFT JOIN table_M m ON k.key1 = m.key1
LEFT JOIN table_Z z ON m.key1 = z.key1 AND m.key2 = z.key2
WHERE (m.val_user LIKE '%Username%' OR z.mes_user LIKE '%User-ID%') AND
(
z.TO_CHAR(mes_date) LIKE '%your_format%' OR
z.TO_CHAR(mes_date) LIKE '%your_format%' OR
z.TO_CHAR(mes_date) LIKE '%your_format%' OR
z.TO_CHAR(mes_date) LIKE '%your_format%');

edited Sep 14 '17 at 07:27

answered Sep 14 '17 at 07:21

Supun Amarasinghe

1,443
3
12
24

thank you but I already used the right date format. I just get way too many datasets. i get data sets from all users in the database at the two given dates. but i only want the data sets from the one user I insert into %Username% and %User-ID%. – wenzel267 Sep 14 '17 at 07:31
Cool.Glad you sorted it out :) – Supun Amarasinghe Sep 14 '17 at 07:40
`TO_CHAR( date_value )` uses the `NLS_DATE_FORMAT` session parameter as the default format model; this is a per-user session setting and should **NOT** be relied on to be consistent and unchanging (especially in international organisations where users in different territories will have different default date formats). Without specifying a format mask the query will break as soon as one user changes their date format and you will have a hard time debugging it as the query will not have changed. – MT0 Sep 14 '17 at 08:56

score 1 · Answer 2 · answered Sep 14 '17 at 08:49

The data type of mes_date and val_date are date.

Using z.mes_date like '%18.04.17%' is performing a string comparison on the date and effectively the same as:

TO_CHAR(
  z.mes_date,
  ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
) like '%18.04.17%'

The NLS_DATE_FORMAT session parameter is a per-user setting and can be changed by each user. Do NOT rely on it to be consistent and unchanging - there lies the road to some very hard to debug bugs where one user changes a setting and suddenly your query breaks without the query changing.

A better solution is:

TRUNC( z.mes_date ) IN ( DATE '2017-04-18', DATE '2017-04-20' )

But that will prevent you using an index on z.mes_date and would require a function-based index on TRUNC( z.mes_date ).

You could also use:

   ( z.mes_date >= DATE '2017-04-18' AND z.mes_date < DATE '2017-04-18' + 1 )
OR ( z.mes_date >= DATE '2017-04-20' AND z.mes_date < DATE '2017-04-20' + 1 )

Which would allow the use of indexes on mes_date.

Serg · Answer 3 · 2017-09-14T09:18:13.993

0

Looks like you need ( assuming those columns are round dates)

select k.info, z.mes_user, z.mes_date, m.val_user, m.val_date
from table_K k
left join table_M m on k.key1 = m.key1 and m.val_user like '%Username%'
left join table_Z z on m.key1 = z.key1 and m.key2 = z.key2
   and z.mes_user like '%User-ID%' 
   and (z.mes_date in (TO_DATE('18.04.17','dd.mm.yy'), TO_DATE('20.04.17','dd.mm.yy')) 
     or z.val_date in (TO_DATE('18.04.17','dd.mm.yy'), TO_DATE('20.04.17','dd.mm.yy')));

edited Sep 14 '17 at 09:18

answered Sep 14 '17 at 07:37

Serg

22,285
5
21
48

This will not work if `mes_date` is `2017-04-18T01:23:45`. – MT0 Sep 14 '17 at 08:50
@MT0 definetly. Added the assumption. Your answer gives the clear perspective for the case the assumption will not hold. – Serg Sep 14 '17 at 09:23

How to select multiple dates with SQL

3 Answers3