Select statement problem
This is what's going on. I need to SELECT a subset of records filtered by adateTime type parameter. It's supposed to be very simple. I ran into a problem of the SELECT statement ignoring majority of records altogether. Instead of expected about 250 records I get only 62. In order to verify if this is the case, I set up a test table and ran the same statement. I tried to make sure that the structures for the two tables: thetest oneand myworking tablebe as closely matched as possible. The statement works on the test table and the result is366 but on my working table I get62 records instead of expected..... I do not know exactly how many I can expect, not 366 of course but close to250. I would be surprised if I could get anything less than that. In my working table the days do not follow calendar strictly. Only weekdays are present. Holidays and weekends are excluded.
I go to my working table and see that many of the records are simply ignored. The dates are there but they do not appear in the result table.
The only difference is that in myworking tablethe dates all have12:00:00 AM for hh:mm:ss.fff. Another mystery is that in theresulting SQL server table records (the ones I get after executingexec sp_sqlexec @SQLat the second statement below) from the working table appear as00:00:00.000.
The whole date in SQL result set looks like2006-03-23 00:00:00.000whereas in the SQL table the same record will look as3/23/2005 12:00:00 AM
USE DailyClose
GO
DECLARE @SQL varchar (256)
DECLARE @cDateLast varchar (23)
DECLARE @cDateFirst varchar (23)
DECLARE @dateTimedLast dateTime
DECLARE @dateTimedFirst dateTime
SET @dateTimedLast = GETDATE()
SET @dateTimedFirst = DATEADD (year, -1, GETDATE())
PRINT @dateTimedLast
PRINT @dateTimedFirst
SET @cDateLast = CONVERT(varchar(23), @dateTimedLast, 121)
SET @cDateFirst = CONVERT(varchar(23), @dateTimedFirst, 121);
PRINT @cDateLast
PRINT @cDateFirst
CREATE TABLE t (
[Id] [int] NOT NULL,
[dateTimed] [dateTime] NULL
);
DECLARE @i int;
DECLARE @ddt dateTime;
SET @i = 1;
SET @ddt = GETDATE();
WHILE @i < 1001 BEGIN
INSERT INTO t ([Id],[dateTimed]) VALUES (@i, @ddt)
SET @i = @i + 1;
SET @ddt = DATEADD (day, -1, @ddt)
END;
SET @SQL = 'SELECT dateTimed FROM t
WHERE dateTimed >= CAST ('''+@cDateFirst+''' AS dateTime) ORDER BY dateTimed '
exec sp_sqlexec @SQL -- this statemetn works fine
SET @SQL = 'SELECT DISTINCT dateTimed FROM dailyCl_DJIA
WHERE dateTimed >= CAST ('''+@cDateFirst+''' AS dateTime) ORDER BY dateTimed '
exec sp_sqlexec @SQL -- this statement gives me absurd results
I want Andrea to note (in case you read this) that I changed the format of SELECT ... WHERE statement by adding the CAST clause. I did it out of desperation so to speak when tried to find a solution. The cast did not make any difference but I left it there anyway.
I hope somebody will suggest a resolution to this strange problem. Strange for me anyway.
Many thanks.
