This query returns the correct results.

```
SELECT 
   a.Company, a.FiscalYear, a.FiscalPeriod,
   a.JournalCode, a.JournalNum, a.JournalLine, a.Description,
   a.JEDate, a.GroupID, a.PostedBy, a.PostedDate,
   a.SourceModule,
   b_1.PartDescription, b_1.PartNum, b_1.PONum,
   b_1.POLine, b_1.VendorID, b_1.VendorNum, b_1.VendorName
FROM   
   epicor905.dbo.GLJrnDtl AS a
LEFT OUTER JOIN 
   (SELECT 
       a.Company, a.TranNum, a.TranDate, a.PartNum, a.WareHouseCode, a.TranQty,
       a.MtlUnitCost, a.ExtCost, a.PONum, a.POLine, a.PORelNum, a.PartDescription,
       a.VendorNum, c.VendorID, c.Name AS VendorName, a.POUnitCost, a.POReceiptQty,
       a.GLTrans, a.PostedToGL, b.JournalNum, b.JournalLine, b.JournalCode, 
       b.FiscalYear
    FROM   
       epicor905.dbo.PartTran AS a
    INNER JOIN 
       epicor905.dbo.TranGLC AS b ON a.Company = b.Company
                                  AND a.SysDate_ = b.Key1
                                  AND a.SysTime = b.Key2
                                  AND a.TranNum = b.Key3
    LEFT OUTER JOIN 
       epicor905.dbo.Vendor AS c ON a.Company = c.Company
                                 AND a.VendorNum = c.VendorNum
    WHERE  
       (b.RelatedToFile = 'PartTran')
       AND (a.PostedToGL = 1 )) AS b_1 ON a.Company = b_1.Company
                                        AND a.JournalCode = b_1.JournalCode
                                        AND a.FiscalYear = b_1.FiscalYear
                                        AND a.JournalNum = b_1.JournalNum
                                        AND a.JournalLine = b_1.JournalLine
WHERE  
    (a.FiscalYear = 2014)

```

The following queries do not work.

They all fail with 

> 
> Conversion failed when converting date and/or time from character string.
> 
> 
> 

I have tried using `ISDATE(col) == 1` in my `JOIN` clause but it didn't seem to help.

Added `TOP(10)`:

```
SELECT TOP(10) a.Company,a.FiscalYear,a.FiscalPeriod,a.JournalCode,a.JournalNum,a.JournalLine,a.Description,
              a.JEDate,a.GroupID,a.PostedBy,a.PostedDate,a.SourceModule,b_1.PartDescription,b_1.PartNum,b_1.PONum,
              b_1.POLine,b_1.VendorID,b_1.VendorNum,b_1.VendorName
FROM   epicor905.dbo.GLJrnDtl AS a
       LEFT OUTER JOIN (SELECT a.Company,a.TranNum,a.TranDate,a.PartNum,a.WareHouseCode,a.TranQty,a.MtlUnitCost,a.ExtCost,
                               a.PONum,a.POLine,a.PORelNum,a.PartDescription,a.VendorNum,c.VendorID,c.Name AS VendorName,
                               a.POUnitCost,a.POReceiptQty,a.GLTrans,a.PostedToGL,b.JournalNum,b.JournalLine,b.JournalCode,b.FiscalYear
                        FROM   epicor905.dbo.PartTran AS a
                               INNER JOIN epicor905.dbo.TranGLC AS b
                                       ON a.Company = b.Company
                                          AND a.SysDate_ = b.Key1
                                          AND a.SysTime = b.Key2
                                          AND a.TranNum = b.Key3
                               LEFT OUTER JOIN epicor905.dbo.Vendor AS c
                                            ON a.Company = c.Company
                                               AND a.VendorNum = c.VendorNum
                        WHERE  ( b.RelatedToFile = 'PartTran' )
                               AND ( a.PostedToGL = 1 )) AS b_1
                    ON a.Company = b_1.Company
                       AND a.JournalCode = b_1.JournalCode
                       AND a.FiscalYear = b_1.FiscalYear
                       AND a.JournalNum = b_1.JournalNum
                       AND a.JournalLine = b_1.JournalLine
WHERE  ( a.FiscalYear = 2014 )

```

*Added Order By*

```
SELECT a.Company,a.FiscalYear,a.FiscalPeriod,a.JournalCode,a.JournalNum,a.JournalLine,a.Description,
       a.JEDate,a.GroupID,a.PostedBy,a.PostedDate,a.SourceModule,b_1.PartDescription,b_1.PartNum,b_1.PONum,
       b_1.POLine,b_1.VendorID,b_1.VendorNum,b_1.VendorName
FROM   epicor905.dbo.GLJrnDtl AS a
       LEFT OUTER JOIN (SELECT a.Company,a.TranNum,a.TranDate,a.PartNum,a.WareHouseCode,a.TranQty,a.MtlUnitCost,a.ExtCost,
                               a.PONum,a.POLine,a.PORelNum,a.PartDescription,a.VendorNum,c.VendorID,c.Name AS VendorName,a.POUnitCost,
                               a.POReceiptQty,a.GLTrans,a.PostedToGL,b.JournalNum,b.JournalLine,b.JournalCode,b.FiscalYear
                        FROM   epicor905.dbo.PartTran AS a
                               INNER JOIN epicor905.dbo.TranGLC AS b
                                       ON a.Company = b.Company
                                          AND a.SysDate_ = b.Key1
                                          AND a.SysTime = b.Key2
                                          AND a.TranNum = b.Key3
                               LEFT OUTER JOIN epicor905.dbo.Vendor AS c
                                            ON a.Company = c.Company
                                               AND a.VendorNum = c.VendorNum
                        WHERE  ( b.RelatedToFile = 'PartTran' )
                               AND ( a.PostedToGL = 1 )) AS b_1
                    ON a.Company = b_1.Company
                       AND a.JournalCode = b_1.JournalCode
                       AND a.FiscalYear = b_1.FiscalYear
                       AND a.JournalNum = b_1.JournalNum
                       AND a.JournalLine = b_1.JournalLine
WHERE  ( a.FiscalYear = 2014 )
ORDER  BY a.JournalNum 

```

Also here is some information about this from epicor.

```
Summary:        XA How to determine Key fields in TranGLC table?
Book:           Support Solutions
Page:           10175MPS

PAGE:  10175MPS  Updated: 11/21/2012

PROBLEM:
XA How to determine Key fields in TranGLC table?  The fields will be different depending on the TranGLC.RelatedToFile.

RESOLUTION:
In order to determine this, you need to find the Primary Index for the RelatedToFile's table.

1. Open Data Dictionary Viewer
2. Tools > Personalization
3. Click in the Indexes grid and select the pulldown on the "Collections" and uncheck the "Hidden" checkbox for PrimaryIndex
4. Select the RelatedToFile table in the Data Dictionary
5. View the Primary Index for that table
**The Key fields will be listed in order separated by a comma.  The Company field is not included as the first Key field in the TranGLC table, so that field can be ignored.**

Example:
TranGLC.RelatedToFile = PartTran
The Primary Index for PartTran = sysdttime
*Note the Index fields for the primary index (Company,SysDate,SysTime,TranNum)*
**Company will not be one of the Key fields**
Key1 = SysDate
Key2 = SysTime
Key3 = TranNum

VERSION:
9.04.503

```