Confused on how to retrieve all transactions up to seconds (elminating milliseconds)

Question

Post reply

Confused on how to retrieve all transactions up to seconds (elminating milliseconds)

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
December 26, 2006 at 1:21 pm

#92155

Fellow DBA's/Developers,
I'm trying to retrieve transactions that meet the criteria of (yyyy/mm/dd/ss) and eliminating milliseconds. The query below will retrieve the records that are equivalent up to milliseconds, but I just want the records that satisfy up to seconds. Please Help!!! Thanks in advance.... -Dave
SELECT
name_on_card_vc,Invoice_Id, trx_hd_key, Approval_Code_CH,
Date_DT, Trans_Type_ID ,total_amt_mn
FROM TrxDetailCard a
WHERE
Date_DT >= '20061101' and Date_DT < '20061223'
AND Trans_Type_ID = 'Sale'
and
Merchant_Key = 1291
and
exists
(
select 'x'
from TrxDetailCard b
where a.name_on_card_vc = b.name_on_card_vc
and a.Invoice_Id = b.Invoice_Id
and a.acct_num_ch = b.acct_num_ch
and a.Date_DT = b.Date_DT
and (a.trx_hd_key != b.trx_hd_key
OR a.Approval_Code_CH != b.Approval_Code_CH))

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Sample data and required output would be nice but here's a guess :

WHERE DateCol >= '1998-01-01 10:00:34' AND DateCol < '1998-01-01 23:59:57'

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 2

Here's the sample data: '2006-12-06 10:26:27.170'

What I'd like to retrieve are the transactions that are in the following format: '2006-12-06 10:26:27'

Excluding milliseconds as shown in this datetime example. All records that meet the same date/timestamp without milliseconds.

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

The data is kept in a varchar field or in a datetime field??

Also you can check out the convert function in books online.

Russel Loski SSCertifiable Points: 7059 More actions · Answer 4

and convert(varchar, a.Date_DT, 120) = convert(varchar, b.Date_DT, 120)

This will find two records if they are in the same second. It doesn't round.

Also, this method will mean that indexes won't be used.

Russel Loski, MCSE Business Intelligence, Data Platform

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 5

Thanks Russel! And thanks for everyone else's input also. It was a "datetime" field BTW. That's exactly what I'm looking for. I'll try it out. Much appreciated!

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

BTW, my version will use an index seek (or won't forbid the use of). But either solution will return the correct data.