Simple DATETIME issue...(?) 2

[damann]

DECLARE @tD DATETIME;
SET @tD = GETDATE();
SELECT count(*) FROM CUSTOMER WHERE status = 'A' AND customerID = '106' AND EFFDT = @tD

Hello all,


See above code, for some strange reason this doesnt work... If I take out the @tD variable at the end of the select and hard code a date in like so '4/2/2005' and execute it alone it works. But if executed it just as the way it is above it doenst work? Puzzling to me - please let me know why if anyone knows...


Thanks,
Leo

 

[RoyVidar]

Try:

[tt]SELECT count(*) FROM CUSTOMER
WHERE status = 'A' AND customerID = '106'
AND EFFDT = convert(varchar, getdate(),112)[/tt]

Roy-Vidar

 

[mrdenny]

The reason that it's not working is because the time is also being passed in the variable. When you return getdate() it returns the current date and time to the milisecond. As Roy showed, you'll need to convert the getdate() data to a date only format.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[damann]

Still doesnt work guys... Can someone take a closer look and see if anything's missing here? Thanks a bunch...

 

[damann]

I overlooked one small thing - today's no longer 4/2/2005 so I had to change the date... I gave both of you guys praises for your valuable posts...


Thanks again...
damann