Datetime Query

Question

Post reply

Datetime Query

huskerwendy

Hall of Fame

Points: 3633
More actions
August 17, 2005 at 6:03 pm

#88629

We are experiencing some errors and are trying to find a pattern as to why it is happening, hence I have been given an assignment to match the UploadData to the ServerErrors. These tables were created from a server error log file and a csv file that I was given for all uploads that have occurred on the server. The tables I have are:
CREATE TABLE UploadData (
UploadDate datetime NULL ,
UploadTime datetime NULL ,
ClientID float NULL ,
ClientDomain varchar (255) NULL ,
UploadFilename varchar (255) NULL ,
FilesizeInBytes float NULL
)
and
CREATE TABLE ServerErrors (
Type varchar (255) NULL ,
ErrorDate datetime NULL ,
ErrorTime datetime NULL ,
Source varchar (255) NULL ,
Category varchar (255) NULL
)
In both tables, the ErrorDate and UploadDate both store the date as mm/dd/yyyy and the ErrorTime and UploadTime are stored as hh:mm:ss AM or PM.
I need to run a query that will match records from the UploadData to the ErrorData but the times are not the same. The error data is recorded after the UploadTime. The error record could be anywhere from 5 seconds to a few minutes later than the upload time. How can I write a query that will select records from the UploadTable that match a record to the ErrorTable where the error record is within 5 seconds to 5 minutes after the upload?
Thanks for your help!

Wendy Schuman

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

SELECT *

FROM

UploadData U

JOIN

ServerError S

ON

S.ErrorDate BETWEEN DATEADD(SECOND, 5, U.UploadTime) AND DATEADD(SECOND, 300, U.UploadTime)

I would have to say this will fetch morethan one error for an upload if two or more errors recorded within 5 minutes. Unless there is a definite relationship it would be impossible to get a exact error row for an upload.

/***********************/

if UploadTime AND ErrorTime does not have date information (I don't know how it would be possible in a datetime column)

SELECT *

FROM

UploadData U

JOIN

ServerError S

ON

CONVERT(DATETIME, CONVERT(VARCHAR, S.ErrorDate, 101) + ' ' + CONVERT(VARCHAR, S.ErrorTime, 109)) BETWEEN

DATEADD(SECOND, 5, CONVERT(DATETIME, CONVERT(VARCHAR, U.UploadDate, 101) + ' ' + CONVERT(VARCHAR, U.UploadTime, 109))) AND

DATEADD(SECOND, 300, CONVERT(DATETIME, CONVERT(VARCHAR, U.UploadDate, 101) + ' ' + CONVERT(VARCHAR, U.UploadTime, 109)))

/*********************/

Regards,
gova

huskerwendy Hall of Fame Points: 3633 More actions · Answer 2

Govinn,

Thanks so much for your help, I'm not quite selecting every record in the error log table, but I'm getting most of them. I'm going to try to tweak the query a little. Yes, the datetime values that are being stored are being stored like this:

2005-05-10 00:00:00.000 for date

1899-12-30 12:20:24.000 and like this for time.

I'm wondering if it wouldn't be better to go ahead and start over with the import and concatenate those two fields for both tables so that they import as a proper datetime field.

Wendy Schuman

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Please, for the love of God, do it!!!!!

(sorry getting sick of seeing this field split).

noeld SSC Guru Points: 96590 More actions · Answer 4

(sorry getting sick of seeing this field split).

You may be relieved to know then that 2005 have DATE and TIME types

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

I can think a date type. We may need to store just the date not time. I don't see a need to have just Time without a date. Is to save space and have the Date as foreign key.

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Maybe... but it won't be 8 bytes each, but you'll still have to put up 2 search conditions when only one is enough.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Date as foreign key??? in what scenario does that fall in?

noeld SSC Guru Points: 96590 More actions · Answer 8

Suppose you need to save times only. it is very handy and you can count how many posts do you get here on how to get rid of the time portion of a date time. The issue is not just space saving but speed and flexibility

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 9

DECLARE @MyDate TABLE

(

DateID INT,

myDate DATE

)

DECLARE @myTime TABLE

(

DateID INT, -- Referencing MyDate.DateID

MyTime TIME

)

Just wondering how to use Time DataType

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

I think I'll stay oldschool on this one for a while... untill I really need to store only the time... maybe the day will come .

noeld SSC Guru Points: 96590 More actions · Answer 11

Just wondering how to use Time DataType

it is just a matter of exploiting less convertions

select directly to a client, add time to a value with a simple "+" sign,.. etc

* Noel