February 1, 2008 at 7:17 am
I'm using SQL server 2000 on 2 different databases. We send image files between 2 different offices and each office uses a different db to log sending and receiving. I'm trying to compare the counts of files on both sides to make sure they match, but I think it's not working correctly.
I'm using:
Select wdFileName_Full,
convert(datetime,wdDate,1) as wdDate,
'Sent' AS Sent
FROM Table1
WHERE wdDate >= (CAST(FLOOR(CAST(GetDate() AS FLOAT))AS DATETIME))
UNION ALL
SELECT DISTINCT SourceFilename, CAST(FLOOR(CAST(Created AS FLOAT)) AS DATETIME) AS Created, 'Received' AS Received
FROM Table2
WHERE Created >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
The dates are both datetime formats, so I'm trying to convert them to just dates, so that I can compare them, but the counts are sometimes off when I know they're correct, so I was hoping someone could tell me if I'm coverting them incorrectly?
Also, is there a way to group the counts on both sides by day, so that my results will just be the day and the 2 counts for each side?
Any help is greatly appreciated, thanks!
February 1, 2008 at 1:15 pm
Why did you use different functions to convert your criteria?
For example,
SELECT convert(datetime,GETDATE(),1)
UNION
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
returns
2008-02-01 00:00:00.000
2008-02-01 14:13:09.733
Is it your problem?
Regarding to your second question, grouping your results by day, have you ever tried to truncate time part in your datetime column, then using GROUP BY clause?
February 1, 2008 at 3:32 pm
How do I truncate the time part in the datetime column? I think that's what I need to do thanks!
Also, how would I specify the previous day? This will be used in a report, which is run daily on the previous day's totals, so I really need to somehow specify the previous day.
Any help is greatly appreciated, thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply