Subscribe Bookmark RSS Feed

Question about SQL query WHERE statement

Hello jmp expert,
I want query one date period data by SQL/database. the period is between A and B, but A and B need to be input by dialog box (keyboard key in).

for example, i want data between 1900-1-1 and 1901-1-1 in my database. need jmp show dialog box to me, and key in the 1900-1-1 and 1901-1-1.

do you have any idea?

thanks
4 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Hello Semineo,


If A and B contain the text strings "1900-1-1" and "1901-1-1", you could build a SQL statement like this (for Oracle):





style="font-size: 10pt; font-family: "Courier New"; color: green;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">dsn_string

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"(YOUR
DATABASE CONNECT STRING GOES HERE)"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">

 





style="font-size: 10pt; font-family: "Courier New"; color: black;">sql_statement

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"select
m.x, m.y, m.z "

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">            

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"
from mytable m "

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">            

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"
where m.start_date
between to_date('"

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">
a

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"',
'YYYY-MM-DD') "

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">            

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"
style="">                       
and to_date('"

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">
b

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"
23:59:59', 'YYYY-MM-DD HH24:MI:SS') "

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">

 





style="font-size: 10pt; font-family: "Courier New"; color: black;">dt

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">open
database

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">dsn_string

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">
sql_statement)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">

 




 




Regards,


Peter
pniel

Community Trekker

Joined:

Jun 23, 2011

hi,

do you know how to build a SQL statement like this but in SQL server ??
pmroz

Super User

Joined:

Jun 23, 2011

A google search for "SQL Server between dates" reveals a wealth of helpful information

http://msdn.microsoft.com/en-us/library/bb264565(v=sql.90).aspx

http://www.databasejournal.com/features/mssql/article.php/2209321/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm

http://www.w3schools.com/SQl/sql_dates.asp

http://msdn.microsoft.com/en-us/library/ms187922.aspx
pniel

Community Trekker

Joined:

Jun 23, 2011

tanks..