SQL Server : JOIN and WHERE

Question

I'm trying to create a new table based on particular values that match between two tables and that works fine but my issue comes about when I try to filter the newly joined table by dates.

CREATE TABLE JoinedValuesTable
(
     [Ref] INT IDENTITY(1,1) PRIMARY KEY,
     [Parties] CHAR(50),
     [Accounts] CHAR(50),
     [Amount] FLOAT
);

The table above is created okay and I join insert values into it by joining two tables like this....

INSERT INTO JoinedValuesTable ([Parties], [Accounts], [Amount])
    SELECT 
        InputPerson.[PARTY], Input_Y.[R_Account_1], InputPerson.[Amount] 
    FROM 
        InputPerson
    JOIN 
        Input_Y ON InputPerson.[Action] = Input_Y.[Action]

And this works fine it's when I try to filter by dates that it doesn't seem to work....

INSERT INTO JoinedValuesTable([Parties], [Accounts], [Amount])
    SELECT 
        InputPerson.[PARTY], Input_Y.[R_Account_1], InputPerson.[Amount] 
    FROM 
        InputPerson
    JOIN 
        Input_Y ON InputPerson.[Action] = Input_Y.[Action]
    WHERE 
        InputPerson.[Date] BETWEEN '2018-01-01' AND '2018-03-03'

I'm not getting any values into my new table. Anyone got any ideas?

There is no chance that either of those queries work as posted. You have three columns listed in the insert statement and 4 columns of data. — Sean Lange, Oct 01 '18 at 14:45
InputPerson.[Date] is the data of type Date Andrew! Ah thanks for spotting that Sean! I just fixed it, got any ideas? The SELECT works as expected and it all does apart from when I include the line that's the where data filter Jarlh — , Oct 01 '18 at 15:24
Yes I have an idea....it is the same idea that @Andrew has. What is the datatype of your Date column? — Sean Lange, Oct 01 '18 at 15:31
I would **strongly** suggest not to use `CHAR(n)` with such big numbers - `CHAR` is **padded to the defined length** with spaces, e.g. it will always occupy 50 characters (bytes) of space - even if you store only `ABC` in it. I'd recommend using `VARCHAR(50)` instead. Also: be careful with `FLOAT` since it's notoriously prone to rounding errors - I'd recommend using `DECIMAL(p,s)` instead which doesn't have these problems. — marc_s, Oct 01 '18 at 16:01

score 3 · Answer 1 · answered Oct 01 '18 at 15:01

3

Do not use between for dates. A better method is:

WHERE InputPerson.[Date] >= '2018-01-01' AND
      InputPerson.[Date] < '2018-03-04'

I strongly recommend Aaron Bertrand's blog on this topic: What do BETWEEN and the devil have in common?

This assumes that Date is being stored as a date/time column. If it is a string, then you need to convert it to a date using the appropriate conversion function.

answered Oct 01 '18 at 15:01

Gordon Linoff

1,242,037
58
646
786

... where the second date is + one day. – Olivier Jacot-Descombes Oct 01 '18 at 15:05
Thank you for the advise and link on how BETWEEN is the devil XDDD but unfortunately it hasn't fixed the issue I'm having :( . Any ideas? – Oct 01 '18 at 15:24
@OverFlowMars . . . Presumably you have no rows that meet that condition. – Gordon Linoff Oct 01 '18 at 18:37

incomudro · Answer 2 · 2018-10-01T15:09:38.723

0

A shameless copy/paste from an earlier answer:

BETWEEN CONVERT(datetime,'2018-01-01') AND CONVERT(datetime,'2018-03-03')

The original answer: Datetime BETWEEN statement not working in SQL Server

I suspect that the datetime versus string is the culprit. Resulting in (without the insert into):

SELECT InputPerson.[PARTY], Input_Y.[R_1], Input_Y.[R_Account_1], InputPerson.[Amount] 
FROM InputPerson
JOIN Input_Y ON InputPerson.[Action] = Input_Y.[Action]
WHERE InputPerson.[Date] BETWEEN CONVERT(datetime,'2018-01-01') AND CONVERT(datetime,'2018-03-03')

-- Edit (after comments from Olivier) --

Are you sure the select-statement returns results? Perhaps the Inner-Join combined with the Where-clause results in an empty result set.

edited Oct 01 '18 at 15:09

answered Oct 01 '18 at 14:46

incomudro

548
4
12

2

You can use strings as date/time constants in SQL-Server. No need to convert. – Olivier Jacot-Descombes Oct 01 '18 at 14:57
See: "Example - With Date" in [SQL Server: BETWEEN Condition](https://www.techonthenet.com/sql_server/between.php) (TechNet). – Olivier Jacot-Descombes Oct 01 '18 at 15:06
It appears you are right (just tested it on a test database). Not sure what the problem is. The between statement works with and without the convert. I start to suspect the the select statement is lacking records. – incomudro Oct 01 '18 at 15:07
Yes it works with or without. But does anyone know why I can't use the WHERE to filter between dates to get the desired INSERT? – Oct 01 '18 at 15:26

SQL Server : JOIN and WHERE

2 Answers2