Understanding OPENQUERY and ticks 1

[MacroAlan]

I have been opening a file every morning for months and manually (or Find and Replace) the date. My buddy suggested a computed value to get the date for me. I am now at the point of putting the whole thing together and having problems understanding the correct number of tick (') marks and the spacing.

     USE Alan_Test                 --Contains copy of real table

DECLARE @yesterday varchar(6)
SELECT @yesterday=CONVERT(varchar(6),getdate()-1,12)  --Use prev date
PRINT @yesterday

INSERT INTO Inventory_Hist 
([DATE_ACCT_YR],[DATE_ACCT_MO],[PART],[LOCATION],[DATE_HISTORY]
      ,[COST],[JOB],[SUFFIX],[SEQ],[DEBIT_PROD_LN],[A50_CUSTOMER])


SELECT *
FROM OPENQUERY(BALCUB,'select *
        FROM BALCWF.Inventory_Hist
        WHERE DATE_HISTORY=''''''+@yesterday+'''''' ')

It compiles but never shows me anything from the linked server. How does one get the tick marks right; trial and error?

Thanks


Alan

 

[philhege]

Passing variables to an OPENQUERY statement is a bit tricky. You could use sp_executesql; it's cleaner.

Check this thread for some ideas.



-----------
With business clients like mine, you'd be better off herding cats.

 

[Olaf Doschke]

To me it just seems to me a matter of data types. Is date_history really a character type field?

Now minus one day is [tt]dateadd(day, getdate(),-1)[/tt]
If DATE_HISTORY is a datetime, you very very very very very rarely will get a match of DATE_HISTORY and that timestamp now minus one day,
so you have two options:
1. You convert both datetimes to a date and compare them, which makes a fit of any two times of yesterday.
2. You convert yesterdays datetime (now-1 day) to the timespan of yesterday 0 AM to today 0 AM and check, whether DATE_HISTORY is between these start and end datetimes.

I would vote for 1, even more so, if DATE_HISTORY is a date itself already.
1.1 Yesterdays date is [tt]cast(dateadd(day, getdate(),-1) as date)[/tt]
1.2 DATE_HISTORYs date is either itself or [tt]cast(DATE_HISTORY as date)[/tt], the latter will work anyway, because casting a date as date is unneccessary, but also doesn't harm.

So the comparison you could do, if DATE_HISTORY is some of the many date/datetime types is
[tt]cast(DATE_HISTORY as date) = cast(dateadd(day, getdate(),-1) as date)[/tt]

If you want to write queries, the first look has to be about the data types, especially when unsure about such comparisons. The second thought about non matching types is which to convert and how to compare. Before we really had date in MSSQL you could only go with option 2 to test a datetime is within a timespan. Yesterday and any date is in fact a timespan.

If DATE_HISTORY really is a character type, then sorry for that excursion, I don't think it'll hurt, though. The best idea then would still be converting the DATE_HISTORY to a date or datetime instead of converting a date to a character string.

Don't judge a book by it's cover, any query tool, aslo SSMS will show you a date or datetime column in some humany readable locale format with year, month, day, hours, minutes, seconds and perhaps AM/PM. But stored in a database is a binary value. A number also isn't stored as the decimal digits you see displayed or written in code, it is encoded in 4 byte for a normal integer, 4 bytes which displayed as ascii or unicode characters would just look as garbage. But the human readable format you get displayed in a query tool result grid/listview is just human readable, its never your goal to get other data looking like that to be able to compare it, in code you compare the data in their machine format, you also compare wo integers by eg int1=int2 and not CAST(int1 as varchar(11)) = CAST(int2 as varchar(11)), why would you first convert 4 bytes to up to 10 digits and a sign each, to then compare these string representations of the numbers?

We need at least one cast here, for the getdate() side, as getdate() unlike the name it suggests is a datetime and not a date. Sidenote: there always has been CURRENT_TIMESTAMP. Aside of that unavoidable cast because of a missing function to return just the current days date, you also only need to cast DATE_HISTORY as a date, if it isn't one. And in this case it's not a cast without a reason, like with the integer to character casting, you need to strip off time.

Bye, Olaf.

 

[Olaf Doschke]

In regard of the main topic by the question alone: The ticks are not due to OPENROWSET at all and are not your problem at all, if you can compare two dates.
But the problem of knowing how many to use is not magic or trial and error: They are delimiters in their main sense and so to be able to have a tick, or single quote, inside a string, MS decided you need two, so for example instead of 'O'Neil' you need to write 'O''Neil', if your end goal is to print out O'Neil. If your goal is to get DATE_HISTORY='YYYYMMDD' for example, and @date is set to 'YYYYMMDD' , then 'DATE_HISTORY='+@date results in DATE_HISTORY=YYYYMMDD and you miss the ticks around YYYYMMDD, so you need what? 'DATE_HISTORY='''+@date+''''. Why? The three ticks after the equal sign are a) two ticks converted to one inside the string and the one end delimiter. The four ticks at the end are the two delimiters of this string and two inside of them resulting in the one tick you really want.

Edit: And this is how the parsing of this works, to decide whether the current position is within a string or outside:
1. start outside
2. encounter a single quote, a string begins, this is not part of the string, just the start delimiter
3. encounter a further single quote when inside a string? Then this might be the end, but only if the next one isn't a single quote, too. Two single quotes are just combined to one, which is becoming part of the string value and the string continues. Any line with odd number of ticks therefore has to be wrong.

A single quote inside a string would otherwise be impossible to have by the definition of it ending a string, being end delimiter and not part of the string. And there are other mechanisms like escaping with \. Escaping also has its quirks, eg you want a single backlash and need to type two. Sounds fammiliar? It's just a bit easier to cope with the meaning of '\\' than of '''', but is '\'' really that much better? Overall, yes, but that's just my oppinion. I like the choice you have in C# to prefix a string with @ to state, it has no escape sequences inside, makes sense for paths. Also a good reason Linux chose the slash for paths instead of backslash.

Bye, Olaf.

 

[MacroAlan]

OK, my date in DATE_HISTORY is expressed as YYMMDD or 160421. The code below works perfectly and returns ~6000 rows.

SELECT *
FROM OPENQUERY(GLOBALCUB,'select *
        FROM GLOBALCWF.Inventory_Hist
        WHERE DATE_HISTORY=160418')

Thanks, looks like I have a bunch of new terms to understand.



Alan