sql server - In sql, how to filter records in two joined tables -

i output invoices made of info in 2 separate data tables linked unique id #. need update service provided in group of invoices (service info contained in table_b) date period (date info contained in table_a).

here's 2 tables joining

table_a

id------|name-----------------|date----------|total--------|  1-------|--abc company--------|--1/1/17------|--$50--------|  2-------|--john smith---------|--3/1/17------|--$240-------|  3-------|--mary jones---------|--2/1/16------|--$320-------|  1-------|--abc company--------|--8/1/16------|--$500-------| 

table_b

table_id (= id table_a)----|-service-----------|unit price--|qty------|    1--------------------------|--service a--------|--$50.00----|--10-----|                                          -- 2--------------------------|--service b--------|--$20.00----|--12-----|                                          -- 3--------------------------|--service b--------|--$20.00----|--16-----|                                          -- 1--------------------------|--service a--------|--$50.00----|--10-----| 

i able join 2 tables using:

select * table_b b inner join table_a on b.table_id = a.id

which results in following:

results

table_id-|-service-----|-unit price-|-qty-|-id--|-name-----|-date----|total--|  1--------|-service a-  |$50.00------|-10--|-1---|-abc co.--|-1/1/17--|$500--|  2--------|-service b-  |$20.00------|-12--|-2---|-john s.--|-3/1/17--|$240--|  3--------|-service b-  |$20.00------|-16--|-3---|-mary j.--|-2/1/16--|$320--|  1--------|-service a-  |&50.00------|-10--|-1---|-abc co.--|-8/1/16--|$500--| 

now, want rows dates greater 12/31/16. however, when add clause date (see below) results don't change.

select * table_b b inner join table_a on b.table_id = a.id date > 12/31/16 

i expect 2 rows services on 1/1/17 , 3/1/17. how can filter rows particular date value in newly joined table?

assuming date contained in column intended storing dates, , not string, try making sure date you're passing in being interpreted date:

select    *     table_b b    inner join    table_a      on b.table_id = a.id     a.date > convert(datetime , '20161231' , 112 ) 

i suspect sqlserver interpreting date 12/31/16 "twelve divided thirty 1 divided sixteen" - floating point number approximately 0.0241935

the way dates handled, internally, convertable floating point numbers representing number (and fraction of) days since point in time, believe 1 jan 1900. hence 0.024 floating point number represent date 35 minutes past midnight on 01 jan 1900.. , that's why results aren't filtering, because dates satisfy clause (theyre later 01-01-1900 00:35)!