Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL String Format - Returns No Data

Posted on 2011-03-13
4
Medium Priority
?
380 Views
Last Modified: 2012-05-11
I'm creating a sql query and have managed to get to this so far.

SELECT  `OptionData`.`Symbol` ,  `OptionData`.`Date`, `OptionData`.`Expiration` ,  `OptionData`.`AdjClose` ,  `OptionData`.`Strike` ,  `OptionData`.`Type` , (`OptionData`.`Bid` +  `OptionData`.`Ask`) /2 AS  `Price` , DATEDIFF(  STR_TO_DATE(`OptionData`.`Expiration`,'%m/%d/%Y') ,  STR_TO_DATE(`OptionData`.`Date`,'%m/%d/%Y') ) AS  `DaysToExp`, `IWM`.`Close` AS `ExpClose` FROM  `PriceData`.`OptionData` AS  `OptionData`, `PriceData`.`IWM` AS `IWM` WHERE  `OptionData`.`AdjClose` -  `OptionData`.`Strike` <  '10' AND  `OptionData`.`AdjClose` -  `OptionData`.`Strike` >  '-10' AND DATEDIFF(  STR_TO_DATE(`OptionData`.`Expiration`,'%m/%d/%Y') ,  STR_TO_DATE(`OptionData`.`Date`,'%m/%d/%Y') ) > '15' AND DATEDIFF(  STR_TO_DATE(`OptionData`.`Expiration`,'%m/%d/%Y') ,  STR_TO_DATE(`OptionData`.`Date`,'%m/%d/%Y') ) < '45' AND STR_TO_DATE(`IWM`.`Date`, '%m/%d/%Y') = STR_TO_DATE(`OptionData`.`Expiration`, '%m/%d/%Y')

I want to select from OptionData all records Where Strike Number is within 10 points of the AdjClose, and in the feild 'ExpClose' get the 'close' from the 'IWM' table for that 'Expiration' Date.

The above seems as though it should work, but returns no records.

IF I were to remove the 'IWM' table data it returns records fine.  And there are matching Dates on the two tables.

Hope this is somewhat clear, there is a lot here.
0
Comment
Question by:_Carthmen_
  • 2
  • 2
4 Comments
 
LVL 1

Expert Comment

by:robbert_nl
ID: 35122867
You say that if the IWM table is empty you get the a result set, so the cause should be found in the last part of the WHERE clause:

STR_TO_DATE(`IWM`.`Date`, '%m/%d/%Y') = STR_TO_DATE(`OptionData`.`Expiration`, '%m/%d/%Y')

What values do these attributes contain? Do they both contain only dates? or also time?

STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE value if the string contains only date part.



0
 

Author Comment

by:_Carthmen_
ID: 35123135
they contain a string, with a date formated at MM/DD/YYYY (VARCHAR) no time
0
 
LVL 1

Accepted Solution

by:
robbert_nl earned 2000 total points
ID: 35123185
When you do these selects you get data and not NULL values?

SELECT STR_TO_DATE(`IWM`.`Date`, '%m/%d/%Y')
FROM `IWM`.`Date`

SELECT STR_TO_DATE(`OptionData`.`Expiration`, '%m/%d/%Y')
FROM `OptionData`.`Expiration`
0
 

Author Closing Comment

by:_Carthmen_
ID: 35125233
Thank you so much.  I had a bad import on that table that formatted all the data in the 'date' field wrong,  it was returning data but was formatted very wrong so there were no matching records to have.

Should have noticed that before, thank you very much.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question