Need to count rows within a timespan

[gabriel]

I'm new to MS SQL having used MySQL for several years now. I just can't figure out the syntax on counting or selecting rows

a) in the Last X (days, weeks, months...)

or

b) between "date X" and "date Y"

I've tried this to get the last week:

SELECT COUNT(*)
FROM contacts
WHERE CONVERT(datetime,timestamp) < (GETDATE() - DATEADD(DAY,7,GETDATE()))

...but I always get a total that includes rows outside the span.

Any suggestions? Thanks!

[rmiao]

Values in timestamp column are not related to date time, you need datetime column in the table.

[nosepicker]

If your column has a timestamp datatype, then rmiao is correct. But if your column is just named "timestamp", then you will still be ok.

First of all, you probably don't want to use the CONVERT function directly on your "timestamp" column because that will slow down your query unnecessarily. Then, your syntax for selecting records earlier than 7 days ago would be something like this:

SELECT COUNT(*)
FROM contacts
WHERE timestamp < DATEADD(DAY, -7, GETDATE() )

Or, you can do this:

SELECT COUNT(*)
FROM contacts
WHERE timestamp < GETDATE() -7

If you just perform an addition or subtraction on a date like this, SQL Server by default will add or subtract days. Even if your "timestamp" column is not of datetime datatype, as long as it is a valid date SQL Server will implicitly do a conversion on it to datetime datatype.

Keep in mind that the "getdate()" function includes a time element. Therefore, if you want to select records before 12 midnight today, you need to use a query like this:

SELECT COUNT(*)
FROM contacts
WHERE timestamp < DATEADD(day, DATEDIFF(day, 0, getdate()), 0)

This will give you a date and time of "2006-12-29 00:00:00.000".

[gabriel]

Thanks - Sadly I have the 'timestamp' column as a timestamp DT (Binary data).

I'll go back and add an actual datetime column. Is there any way to extract a date/time from the timestamp? It would be nice to be able to capture the data from the records that already exist and get their date and time into the new column.

Maybe ...

UPDATE table 
SET datetime = ???(timestamp)

...where ??? is some magic function that converts a timestamp to a datetime?

[rmiao]

No, sql doesn't generate value based on system time for timestamp column.

[gabriel]

Well thanks anyway. I've made the changes and the app is up and running.

[webchetan]

Hi - The article is good. I am facing problem, with XML files which I am having...someof them have errors, so I am logging error using TRY...CATCH block inside catch blokc, now once it goes to the catch block and I record the filename which has error, I want it to go to the next file in the Folder hierarchy. How can I do that? Because if I have 10 XML files in folder C:\Data...and if the 5th file in the folder is not an XML readeable file...then it goes to catch block, which is okay, but how would I process the remaining files??? Can someone help me out ...I would really appreciate it!

In short :
How can I delete/move the file which has error using XP...CMDShell and I want the stored proc to process the remaining files in the folder....

Thanks a bunch.

Chetan

[webchetan]

How can I delete/move the file which has error using XP...CMDShell and I want the stored proc to process the remaining files in the folder....

Thanks