Query Returns Wrong Date Information

[dwg23]

Hello,
I have written the below query which works really great with the exception that it returns information from the year 2010 instead of 2015.
I have tried removing everything in the where clause except the => '04/01/2015' and still get 2010 info. any ideas?

Thanks,
dwg23

Select distinct
  tx.TxType,
  tblapVoucher.PONumber,
  tblapVoucher.InvoiceDate,
  tblapVoucher.InvoiceAmount,
  tblapVoucherLine.POLineNumber,
  tblapVoucherLine.Qty,
  tblapVoucherLine.UnitCost,
  tblapVoucherLine.ExtendedCost,
  tblapVendor.Name
From
  tblglTxHistory tx Inner Join
  tblapVoucherLine On Left(tx.TxNumber, 8) = tblapVoucherLine.VoucherNumber
  Inner Join
  tblapVoucher On tblapVoucherLine.VoucherNumber = tblapVoucher.VoucherNumber
  Inner Join
  tblapVendor On tblapVoucher.Vendor = tblapVendor.Vendor
Where
  tx.TxSource = 'apvchr' And
  tx.TxDate >= '2015/04/01' And
  tx.TxDate <= '2015/04/30'

 

[gmmastros]

What is the data type of the TxDate column?

Select	DATA_TYPE 
From	Information_Schema.Columns
Where	column_Name = 'TxDate'
		And Table_Name = 'tblglTxHistory'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dwg23]

gmmastros,
it is a datetime field.
Thanks,
dwg23

 

[dwg23]

If I use tblapVoucher.InvoiceDate as my date I get no results.
Seems strange as both columns have info in them.

 

[gmmastros]

When dealing with dates, it is best to use an unambiguous format.

Basically, there are "rules" that are applied when dealing with dates. More importantly, when mixing strings and dates. For example... take a look at this:

Declare @DateTime DateTime

set language italian;
set @DateTime = '2015/04/01'
Select @DateTime

set language us_english;
set @DateTime = '2015/04/01'
Select @DateTime

If you use the format YYYYMMDD, SQL Server will always interpret it the same way. Like this:

Declare @DateTime DateTime

set language italian;
set @DateTime = '20150401'
Select @DateTime

set language us_english;
set @DateTime = '20150401'
Select @DateTime

Basically, I recommend that you format your dates with YYYYMMDD like I show above.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

In lieu of what George said your where clause should rather be

tx.TxDate between '20150401' And '20150501'

Notice, as your TxDate is type datetime the time portions of the date literals will be set to midnight. So this will exclude any datetime on first May, as the max value in that range is the midnight of this day only, and you most probably have no data at exactly that time.

Dealing with datetimes needs you to think in time, not only dates, with a max of '2015/04/30' you are excluding data of that whole day.

Only if you don't make use of the time portion and all dates are stored with midnight time, the situation changes a bit, including 30th of April then is sufficient, as that includes this day midnight. But for over 5 years SQL Server offers the date type for that and you should make use of it. If you want to be more precise this will cover it:

tx.TxDate>='20150401' And tx.TxDate<'20150501'

This range starts (including) midnight of 1st April and ends at (excluding) midnight of 1st May.

Bye, Olaf.

 

[Olaf Doschke]

You should also check, if the joining of data is sufficient.

The date you select, tblapVoucher.InvoiceDate, is not tx.TxDate, the join you make is on Left(tx.TxNumber, 8) = tblapVoucherLine.VoucherNumber, that might join any tblapVoucherLine also from other years.

Bye, Olaf.

 

[dwg23]

To all,
I have to say an excellent education on the use of datetime, but unfortunately it did not help with the problem, still getting dates with the 2010.
Olaf,
the only way to make the join is with the Left(tx.TxNumber,8) this is the first 8 digits of the transaction number which correspond to the voucher number.

Thanks,
dwg23

 

[Olaf Doschke]

So which column shows dates of 2010? InvoiceDate is coming from tblapVoucher. Whatever you filter in blglTxHistory.TxDate has nothing to do with that date.
Check your data in the single tables, it all points to an error in the data, if you get unexpected results. No SELECT query will fix that, it'll only show it.

Bye, Olaf.

 

[dwg23]

Ok,
I am now officially an idiot!
just realized that I was joining to the wrong column!

Thanks for all the help, really was a good education in the datetime.