SQLTeam.com | Weblogs | Forums

Convert date to year when join tables

Hi all,

When I join two tables, convert date time to year and compare to year as string, I can not able to display date time in results so I can not able to filter neither re-order.
Query:

use [HT_V1]

select [trum114].[SYM_DOK]
        ,[trum114].[NR_DOK]
        ,[RUMAG14].[DATA]
	  ,[trum114].[ROK]
	  ,[TRUM114].[TOWAR]
	  ,[TRUM114].STAN
	  ,[trum114].[STANMAG]
	  ,[TRUM114].[DOK_PRZYJ]
     
	 FROM [dbo].[RUMAG14] right join [dbo].[TRUM114] on RUMAG14.[NR_DOK] = TRUM114.NR_DOK
	 and  year([rumag14].[DATA]) = [trum114].[rok]

Why the Sql-transaction block display the date time value?

See the picture to clear understand problem.

Highlighted records must be sort as bellow:

ID TOWAR STAN SYM_DOK NR_DOK
4 CZESCI.....3 8 Bo 1400002 (stock open in February)
3 CZESCI.....3 8 RW 1400013 (item taken from stock in January)
2 CZESCI.....3 9 RW 1400012 (item taken from stock in January)
1 CZESCI.....3 10 PW 1400001 (stock open in January)

Table: [Rumag14] have data time column so is possible to filter
Table: [Trum114] have only year column

Sorted.
There was no match records between two tables, that's why data was not displayed.
When I change convert data time to (two digit year) as below:

SUBSTRING(convert(varchar(20),DATA,120),3,2)

all dates are shown now.

Thanks