February 4, 2013 at 4:07 pm
Hello all!
I am trying to run the following query
The requirements are to compare all the records created today vs C.dcreated property on the system. Because both are datetime formats , I am trying to get the first date as @d_today and the date created as d_created.
Because there are a subquery in a query, I declare Begin-End as a store procedure, not sure if this is the correct way to approach it.
I am getting the following error:
Invalid column name 'd_created'.
Could you help me what will be the correct approach to solve this problem?
Thanks a lot!!
Begin
declare @d_today datetime
set @d_today=(select CONVERT(VARCHAR(10),GETDATE(),101))
select CONVERT(VARCHAR(10),C.dcreated,101) as d_created from C
SELECT C.aCustID, C.dCreated, C.tEmail, C_Sis.tEmailAddress,
tblObjectType3_6.FldString25226
FROM C LEFT OUTER JOIN C_Sis ON C.aCustID = C_Sis.nID LEFT OUTER JOIN
tblObjectType3_6 ON C.aCustID = tblObjectType3_6.nID
where C.dCreated=d_created
end
February 4, 2013 at 4:36 pm
Not really sure what you're trying to do here, but the query that has the column d_created is a different query than the second one. The c alias in the second query can't reference the d_created column.
declare @d_today datetime
select @d_today = GETDATE()
select CONVERT(VARCHAR(10),C.dcreated,101) as d_created from C
SELECT C.aCustID, C.dCreated, C.tEmail, C_Sis.tEmailAddress,
tblObjectType3_6.FldString25226
FROM C LEFT OUTER JOIN C_Sis ON C.aCustID = C_Sis.nID LEFT OUTER JOIN
tblObjectType3_6 ON C.aCustID = tblObjectType3_6.nID
where C.dCreated=@d_today
or
declare @d_today datetime
select @d_today = GETDATE()
SELECT C.aCustID, C.dCreated, C.tEmail, C_Sis.tEmailAddress,
tblObjectType3_6.FldString25226
FROM C LEFT OUTER JOIN C_Sis ON C.aCustID = C_Sis.nID LEFT OUTER JOIN
tblObjectType3_6 ON C.aCustID = tblObjectType3_6.nID
where C.dCreated=getdate()
Just a couple of points.
Converting the dates to character strings and then comparing them is horribly inefficient if both are datetime to begin with.
You probably don't want to do an equal match here. If you're looking to find all the things that happened today and your data has time on it, you'll need to strip the time off the getdate() and use a range compare for dates >= the getdate() and < the next day.
So maybe:
declare @d_today datetime
select @d_today = DATEADD(dd,0, DATEDIFF(dd,0, GETDATE())); -- strips off time
select @d_tomorrow = dateadd(dd,1,@d_today)
SELECT C.aCustID, C.dCreated, C.tEmail, C_Sis.tEmailAddress,
tblObjectType3_6.FldString25226
FROM C LEFT OUTER JOIN C_Sis ON C.aCustID = C_Sis.nID LEFT OUTER JOIN
tblObjectType3_6 ON C.aCustID = tblObjectType3_6.nID
where C.dCreated=@d_today >= @d_today and C.dCreated < @d_tomorrow
February 4, 2013 at 5:03 pm
Hi,
From what I understand from your query is that you want to compare the C.dCreated field for currentdate and fetch data. Hope below should solve the problem.
Begin
--declare @d_today datetime
--set @d_today=(select CONVERT(VARCHAR(10),GETDATE(),101))
--select CONVERT(VARCHAR(10),C.dcreated,101) as d_created from C [highlight=#ffff11]--The alias d_created cannot be used anywhere else out of this query, hence no point in doing this.[/highlight]
SELECT C.aCustID, C.dCreated, C.tEmail, C_Sis.tEmailAddress,
tblObjectType3_6.FldString25226
FROM C LEFT OUTER JOIN C_Sis ON C.aCustID = C_Sis.nID LEFT OUTER JOIN
tblObjectType3_6 ON C.aCustID = tblObjectType3_6.nID
where
CONVERT(VARCHAR(10),C.dcreated,101)=CONVERT(VARCHAR(10),GETDATE(),101) [highlight=#ffff11]--Compare the date part of the C.dcreated and currentdate [/highlight]
end
February 5, 2013 at 8:39 am
Thanks a lot!! both of your solution worked. and yes it can be tricky compare 2 dates.
Thanks again!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply