ABS Function and Datetime

Question

Post reply

ABS Function and Datetime

Rob-350472

SSCertifiable

Points: 7197
More actions
December 11, 2008 at 2:41 am

#132857

I have two tables with a number of datetime records - basically I want to select the closest match from one table when evaluated against a date from the other.
I looked at this:
http://pratchev.blogspot.com/2007/09/use-abs-to-find-closest-match.html
Which worked fine with the example, but did not seem to behave with datetime fields, giving the following error:
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
Here's what I was attempting to do:
DECLARE @Rate_Date Datetime
SET @Rate_Date = '31 Oct, 2006'
SELECT TOP 1 *
FROM tblRates_TESTING
WHERE rate_type = 'ABC'
ORDER BY ABS(rate_date - @rate_date)
Now, tblrates_testing should have a few values for ABC, more specifically a 30-Sep-06 and a 31-Dec-06 - in this case I would like it to return 30-Sep-06 as this is closest to the supplied value of 31-Oct-06
I think it's something silly I am doing wrong, can anyone enlighten me?!

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try

ORDER BY ABS(DATEDIFF(dd, rate_date, @rate_date))

If there are several rows with the same rate_date then you might need the result from DATEDIFF in hours or even minutes.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 2

Hi Chris,

There will never be (or certainly should not be!) any duplicates as regards rate_date and rate_type.

I tinkered and tried the following:

DECLARE @Date_Reported int

set @Date_Reported = (SELECT top 1 convert(int,date_reported) FROM tblA WHERE date_reported = '31 Oct, 2006')

SELECT top 1 *

FROM tblB

WHERE date_reported is not null

ORDER BY ABS(convert(int,Date_Reported) - @Date_Reported)

And it gave me the result I was expecting, that seems a somewhat round about approach though!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

So, you want to get the closest date to your reference date @Date_Reported from table A - then get the closest match to the date found in table A, in table B?

I'm getting a little confused because

set @Date_Reported = (SELECT top 1 convert(int,date_reported)

FROM tblA

WHERE date_reported = '31 Oct, 2006')

will return convert(int, '31 Oct, 2006')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 4

Ok, Ill give this a go...!

--Create table with currency rates:

CREATE TABLE [dbo].[tblRates_TESTING](

[currency_id] [int] IDENTITY(1,1) NOT NULL,

[currency_type] [varchar](10) NULL,

[currency_rate] [decimal](18, 6) NULL,

[rate_date] [datetime] NULL

)

INSERT INTO tblRates_TESTING VALUES('GBP', '0.5', '31 Dec, 06');

INSERT INTO tblRates_TESTING VALUES('GBP', '0.4', '30 Sep, 06');

--Create table with data:

CREATE TABLE [dbo].[tblData_TESTING](

[data_id] [int] IDENTITY(1,1) NOT NULL,

[data_type] [varchar](10) NULL,

[currency_type] [varchar](10) NULL,

[rate_date] [datetime] NULL

)

INSERT INTO tblData_TESTING VALUES('Cash', 'GBP', '31 Oct, 06');

Now, that's essentially what I am dealing with, but with a lot more data and rates/currencies etc.

Basically I want to pull out the closest currency conversion rate (tblRates_TESTING.Currency_Rate) for the date in tbldata_TESTING

So, in this simplified example it should bring back 30-Sep-06.

Now, here was my 1st attempt:

Declare @Rate_Date datetime

SET @Rate_Date = (SELECT top 1 Rate_Date FROM tbldata_Testing)

SELECT top 1 * FROM tblrates_testing ORDER BY ABS(Rate_Date - @Rate_Date)

Which gives the error above:

'Msg 257, Level 16, State 3, Line 5

Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.'

So, my 2nd attempt:

Declare @Rate_Date int

SET @Rate_Date = (SELECT top 1 convert(int,Rate_Date) FROM tbldata_Testing)

SELECT top 1 * FROM tblrates_testing ORDER BY ABS(convert(int,Rate_Date) - @Rate_Date)

Provides the data I expect - namely the row which has 30-Sep-06 date.

Now, what I am saying is that it seems (slightly) round the houses to convert the datetime fields to int fields to get the result, so, am I missing something?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Reckon this should do it:

[font="Courier New"]--Create table with currency rates:

DROP TABLE #tblRates

CREATE TABLE #tblRates(

[currency_id] [int] IDENTITY(1,1) NOT NULL,

[currency_type] [varchar](10) NULL,

[currency_rate] [decimal](18, 6) NULL,

[rate_date] [datetime] NULL

)

INSERT INTO #tblRates

SELECT 'GBP', '0.07', '30 Jul, 06' UNION ALL

SELECT 'GBP', '0.08', '30 Aug, 06' UNION ALL

SELECT 'GBP', '0.09', '30 Sep, 06' UNION ALL

SELECT 'GBP', '0.10', '30 Oct, 06' UNION ALL

SELECT 'GBP', '0.11', '30 Nov, 06' UNION ALL

SELECT 'GBP', '0.12', '31 Dec, 06'

-- Data table:

DROP TABLE #tblData

CREATE TABLE #tblData(

[data_id] [int] IDENTITY(1,1) NOT NULL,

[data_type] [varchar](10) NULL,

[currency_type] [varchar](10) NULL,

[rate_date] [datetime] NULL

)

INSERT INTO #tblData

SELECT 'Cash', 'GBP', '18 Aug, 06' UNION ALL

SELECT 'Cash', 'GBP', '25 Oct, 06'

-- method 1:

SELECT d.*, currency_rate = (SELECT currency_rate FROM #tblRates WHERE rate_date =

(SELECT TOP 1 r.rate_date FROM #tblRates r ORDER BY ABS(DATEDIFF(dd, r.rate_date, d.rate_date))))

FROM #tblData d

-- method 2:

SELECT d.*, r.*

FROM #tblData d, #tblRates r

WHERE r.rate_date = (SELECT TOP 1 rate_date FROM #tblRates ORDER BY ABS(DATEDIFF(dd, rate_date, d.rate_date)))

[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 6

Ah, I see, essentially the DATEDIFF function gets around me having to convert the dates to int fields - nice 🙂

Thanks Chris!