help with filtering dates (returns no records)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with filtering dates (returns no records)

Author

Topic

koturtle
Starting Member

4 Posts

Posted - 2010-09-21 : 14:23:59

Ok, i am no expert in SQL (obv). I have a query that creates a date
field ([d]). The query is below.

What i need to do is filter this new date field ([d]) for records less than 8/1/10. When i try to add a where clause that says [d]<'8/1/10' it gives me no records. I think it has to do with the date field being a string rather than a datefield but i don't know how to deal with it.

query-

SELECT Expr1 AS TotalDollars, CAST(CAST('1/31/' + CAST(Year AS
varchar(4)) AS datetime) AS sql_variant) AS D, ValueTypeDesc,
ResourceCode,
CostElementTypeDesc, ProjProjectID
FROM dbo.TotalCosts
UNION ALL
SELECT Expr2, CAST(CAST('2/28/' + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_11
UNION ALL
SELECT Expr3, CAST(CAST('3/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_10
UNION ALL
SELECT Expr4, CAST(CAST('4/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_9
UNION ALL
SELECT Expr5, CAST(CAST('5/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_8
UNION ALL
SELECT Expr6, CAST(CAST('6/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_7
UNION ALL
SELECT Expr7, CAST(CAST('7/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_6
UNION ALL
SELECT Expr8, CAST(CAST('8/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_5
UNION ALL
SELECT Expr9, CAST(CAST('9/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_4
UNION ALL
SELECT Expr10, CAST(CAST('10/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_3
UNION ALL
SELECT Expr11, CAST(CAST('11/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_2
UNION ALL
SELECT Expr12, CAST(CAST('12/1/' + + CAST(Year AS varchar(4)) AS
datetime) AS sql_variant) AS [$2], ValueTypeDesc, ResourceCode,
CostElementTypeDesc,
ProjProjectID
FROM dbo.TotalCosts AS dbo_totalcosts_1

any help would be great!
thanks,
Koturtle

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-21 : 14:34:02

Does that even compile? String date manipulation and sql_variants.. better take a step back and try breaking the problem down. First, do you need to apply the WHERE clause to each select or just the results of all the UNION operator?

maybe this link will help you give us DDL, DML and expected results so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

GhantaBro
Posting Yak Master

215 Posts

Posted - 2010-09-21 : 14:37:10

One simple wasy to do this is

where convert(varchar, [d], 112) < '20080110'

What is the data-type for [d]?

koturtle
Starting Member

4 Posts

Posted - 2010-09-21 : 14:48:31

I'm a bit embarassed to admit that i don't really understand even what the datatype is. I inherited this problem and really only know Access but am trying to help out.
I have a table that has values in buckets called (Jan, Feb, Mar etc) with a year field also. The query above helps create a date field. It does compile

!
Here is how the data sits in the table:

Jan Feb Mar Year
5 5 2 2010
3 10 8 2011

so this query above creates a union query that puts the data as follows.
5 1/1/10
5 2/1/10
2 3/1/10
3 1/1/11
10 2/1/11
8 3/1/11

I thought that the query above was making the date field a "datetime" field just by looking at the query but i am probably wrong.
i don't mind putting the where clause a query that is created from the outcome of this query but i can't get that to work either.

Thanks for the help

koturtle
Starting Member

4 Posts

Posted - 2010-09-21 : 14:54:17

quote:
Originally posted by GhantaBro

One simple wasy to do this is

where convert(varchar, [d], 112) < '20080110'

What is the data-type for [d]?

I get an "incorrect syntax near the keyword 'Where' error.

thanks,
KO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-21 : 16:19:25

You haven't supply proper sample data so we have nothign to run queries against. Please follow the link I posted before to helkp us help you.

I modified the fist select statement you provoded to try to meet your requirement([d]<'8/1/10'). But, I'm still not clear if you want it for just one select, for some selects or to filter the result of all the unions.

SELECT 
	Expr1 AS TotalDollars, 
	DATEADD(YEAR, Year - 1900, '1900-01-31') AS D
	ValueTypeDesc, 
	ResourceCode, 
	CostElementTypeDesc, 
	ProjProjectID 
FROM 
	dbo.TotalCosts 
WHERE
	'2010-08-01' > DATEADD(YEAR, Year - 1900, '1900-01-31')

koturtle
Starting Member

4 Posts

Posted - 2010-09-21 : 17:58:15

This actually helped tremendously.
Thanks for your time i really really appreciate it.
I would have been lost forever!

thanks,
KO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources