DateTime Query Problem

[Derice]

Column A (datetime)
-----------------------
2011-04-01 00:00:00.000
2011-04-01 00:00:00.000
2011-04-09 00:00:00.000
2011-04-01 00:00:00.000
2011-03-31 00:00:00.000

Column B (datetime)
-----------------------
2011-03-25 15:45:56.457
2011-03-25 15:52:37.933
2011-03-25 15:54:37.197
2011-03-25 16:05:30.023
2011-03-27 00:43:23.713

When i try to query the above record

Select * from table where A between '1900-01-01' and '2012-01-01'

this work fine.

Select * from table where A between '1900-01-01' and GetDate()

this is not working. 0 record return.

Select * from table where B between '1900-01-01' and '2012-01-01'
Select * from table where B between '1900-01-01' and GetDate()

Both work fine for column B. Column B are filled with time but not for column A.
FYI, it is a mssql 2008 R2 database.

Is there way to solve this problem?

[Knvn]

I executed the below query on MS-SQL 2008 and it worked as expected.

DECLARE @table as table(A datetime,B datetime)

insert into @table values('2011-04-01 00:00:00.000','2011-03-25 15:45:56.457')
insert into @table values('2011-04-01 00:00:00.000','2011-03-25 15:52:37.933')
insert into @table values('2011-04-09 00:00:00.000','2011-03-25 15:54:37.197')
insert into @table values('2011-04-01 00:00:00.000','2011-03-25 16:05:30.023')
insert into @table values('2011-03-31 00:00:00.000','2011-03-27 00:43:23.713')

Select * from @table where A between '1900-01-01' and '2012-01-01'
Select * from @table where A between '1900-01-01' and GetDate()
Select * from @table where B between '1900-01-01' and '2012-01-01'
Select * from @table where B between '1900-01-01' and GetDate()

I guess in your system, column A's date is not falling inside your Systems date which is returned by GetDate().

[Derice]

Thanks for the testing effort. It was my fault as I am expecting record(s) return from this sql statement while i am executing it before 2011-03-31.

SELECT * FROM TABLE WHERE A BETWEEN '1900-01-01' AND GetDate()