select data only by month and year

Question

I have a table with month and year:maintable

What I want is a range selection: result

I thought between would be my friend and I tried the following:

SELECT        jahr, monat, alles
FROM            dbo.table
WHERE        (jahr BETWEEN 2017 AND 2018) AND (monat BETWEEN 11 AND 2)

But this does not work. How can I get what I want?

`BETWEEN` boundaries have to have the low number on the left and the higher number on the right. As written, your second `BETWEEN` clause will never evaulate to true. You could either use `NOT BETWEEN` or you could use an `IN` clause instead — Xedni, Mar 23 '18 at 17:00
I know it's not usually feasible to refactor the data structure, but there's a specific data type for this: `date`. It would be better suited to this task. It would simplify the query and (as a bonus), save a bit of space. — Steven Hibble, Mar 23 '18 at 17:11
with this solution i get also the combination 2017 1 and 2017 2 — frank, Mar 23 '18 at 17:14
Possible duplicate of [Convert month and year combination to date in SQL](https://stackoverflow.com/questions/22138553/convert-month-and-year-combination-to-date-in-sql) — Tab Alleman, Mar 23 '18 at 18:48

score 2 · Answer 1 · answered Mar 23 '18 at 16:59

2

Assuming that jahr and monat are integer formats you can make a key out of them and can do a comparison that way. You essentially end up with YYYYMM format.

SELECT        jahr, monat, alles
FROM            dbo.table
WHERE (jahr*100) + monat between 201711 and 201802

answered Mar 23 '18 at 16:59

jradich1234

1,410
5
24
29

this is nice solution, but i have a problem with the plus sign. what to do with the plus sign in dynamic sql? – frank Mar 23 '18 at 18:03
@frank if the plus sign in inside your single quote it shouldn't be an issue. Just make sure it's treated as a part of the text like you other reserved words (select, from, etc....) – jradich1234 Mar 23 '18 at 18:09
i wrote it that way, everything inside the string: – frank Mar 23 '18 at 18:12
i wrote it that way, everything inside the string: SET query = 'select * from table WHERE (jahr*100) + monat between ' + von + ' and ' + bis + '. error is: error converting nvarchar 'select * from table WHERE (jahr*100) + monat between ' in int datatype, please imagin the at-sign in front of variables – frank Mar 23 '18 at 18:17
@frank ok I see your issue. You need to convert von and bis to varchar inorder to concatenate them like strings – jradich1234 Mar 23 '18 at 18:26

Hiran Perera · Answer 2 · 2018-03-23T17:51:39.723

You can compare the two dates as simple as follows in different conditions.

Compare the date is above the 2017 November (mont <=12 is used as a safety check, you can ignore that if you can guarantee the month column doesn't contain values greater than 12).

jahr >= 2017 AND monat >= 11 AND mont <=12

else you can use between jahr >= 2017 AND monat between 11 AND 12

Compare the date is below the 2018 February (mont >= 1 is used as a safety check, you can ignore that if you can guarantee the month column doesn't contain values less than 1).

jahr <= 2018 AND monat >= 1 AND mont <=2

else you can use between

jahr >= 2017 AND monat between 1 AND 2

The Whole condition with simple operators

(jahr >= 2017 AND monat >= 11 AND monat <=12) AND (jahr <= 2018 AND monat >= 1 AND monat <=2)

With between conditions

(jahr >= 2017 AND monat between 11 AND 12) AND (jahr >= 2017 AND monat between 1 AND 2)

Following is the exact sql for your problem. All three queries should work for your purpose.

-- With simple operators
SELECT        jahr, monat, alles
FROM          dbo.table
WHERE         (jahr >= 2017 AND monat >= 11 AND monat <=12) 
AND (jahr <= 2018 AND monat >= 1 AND monat <=2)

-- With simple operators (Without security boundary checks)
SELECT        jahr, monat, alles
FROM          dbo.table
WHERE         (jahr >= 2017 AND monat >= 11) 
AND (jahr <= 2018 AND monat <=2)

-- With between operator
SELECT        jahr, monat, alles
FROM          dbo.table
WHERE         (jahr >= 2017 AND monat between 11 AND 12)
AND (jahr >= 2017 AND monat between 1 AND 2)

Jay Shankar Gupta · Answer 3 · 2018-03-23T17:05:36.340

1

SELECT        jahr, monat, alles
FROM           dbo.table
WHERE (jahr = 2017 AND monat IN (11,12))
      OR (jahr = 2018 AND monat IN (1,2))

Output

jahr    monat   alles
2017    11      105
2017    12      105
2018    1       104
2018    2       105

Demo Link

http://sqlfiddle.com/#!18/13a69/1

edited Mar 23 '18 at 17:05

answered Mar 23 '18 at 16:59

Jay Shankar Gupta

5,918
1
10
27

Hogan · Accepted Answer · 2018-03-23T18:39:19.430

1

I would do it like this

SELECT  jahr, monat, alles 
FROM table1
WHERE cast(cast(jahr as varchar(20))+'-'+cast(monat as varchar(20))+'-01' as date) >=  '2017-11-01'
 and cast(cast(jahr as varchar(20))+'-'+cast(monat as varchar(20))+'-01' as date) <='2018-02-01'

I think this is the best solution because you having the server deal with the details -- you are taking your data model and converting to a date type and then having the server do a date compare for the filter.

http://sqlfiddle.com/#!18/13a69/21/0

Debug

http://sqlfiddle.com/#!18/13a69/20/0

edited Mar 23 '18 at 18:39

answered Mar 23 '18 at 17:25

Hogan

69,564
10
76
117

tried it also with plus sign instead of pipe, getting the error message that explicit converting from int to date is not allowed – frank Mar 23 '18 at 18:09
Looks like pipe pipe (||) is oracle only – KeithL Mar 23 '18 at 18:13
@keithl -- pipe pipe is sql standard for concat strings. – Hogan Mar 23 '18 at 18:15
@frank I changed it above to cast your integers to strings before the concatenate. – Hogan Mar 23 '18 at 18:16
@frank -- I used jay's sql fiddle to get the syntax exactly right – Hogan Mar 23 '18 at 18:39

score 0 · Answer 5 · answered Mar 23 '18 at 17:07

0

SELECT        jahr, monat, alles
FROM            dbo.table
WHERE        ( jahr = 2017 AND monat BETWEEN 11 AND 12 )
             OR 
             ( jahr = 2018 AND monat BETWEEN 1 AND 2 )

answered Mar 23 '18 at 17:07

kc2018

1,440
1
8
8

score 0 · Answer 6 · answered Mar 23 '18 at 17:24

You can try the following query.

SELECT        jahr, monat, alles
FROM           table1
WHERE CASE WHEN (jahr = 2017 AND monat BETWEEN 11 AND 12) THEN 1
            WHEN (jahr = 2018 AND monat BETWEEN 1 AND 2) THEN 1 
            ELSE 0 END = 1

Thanks

score 0 · Answer 7 · answered Mar 23 '18 at 18:20

You could use the OR operator and some parenthesis:

DECLARE @maintable TABLE (jahr int, monat int, alles int)
INSERT INTO @maintable VALUES
(2017, 7, 189),(2017, 8, 125),(2017, 9, 92),(2017, 10, 98),(2017, 11, 105)
,(2017, 12, 105),(2018, 1, 104),(2018, 2, 105),(2018, 3, 99)

SELECT jahr
      ,monat      
      ,alles 
  FROM @maintable
WHERE (jahr >= 2017 AND monat >= 11)
      OR
      (jahr <= 2018 AND monat <= 2)

Produces:

jahr    monat   alles
2017    11      105
2017    12      105
2018    1       104
2018    2       105

select data only by month and year

7 Answers7