?
Solved

SQL query logic

Posted on 2006-05-18
11
Medium Priority
?
705 Views
Last Modified: 2008-01-09
SQL Query Needed

I have a table containing dates in text datatype

SCDATA (tableName)
_______

ASOF      (varchar2)
****
03-APR-06      
19-APR-06      
24-APR-06      
25-APR-06      
26-APR-06      
27-APR-06      
01-MAY-06      
02-MAY-06      
04-MAY-06      
09-MAY-06      
05-MAY-06      
08-MAY-06      
15-MAY-06      
10-MAY-06      
12-MAY-06      
17-MAY-06      


I want a sql query which will calculate the below logic
 if (example 1)  I choose 17-May-06 the query should return 01-MAY-06
if  I choose 27-APR-06 the query should return 03-APR-06
  (ie) the query should return the first day or greater of the current month and is if that date is present in the table

Hope I am clear

Thanks
0
Comment
Question by:gak2004
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16710457
Hi gak2004,

Which database engine are you using?

First, you will need to convert your date as a Date field.

I would try something like this:

select min (cast(asof as datetime))
FROM scdata
where year(cast(asof as datetime)) = 2006 and month(cast(asof as datetime)) = 4

Cheers!
0
 
LVL 14

Expert Comment

by:nayernaguib
ID: 16710599
If you are using SQL Server, the following query should work:

SELECT TOP 1 ASOF
FROM SCDATA
WHERE ASOF like '%' + @month + '%'
ORDER BY ASOF;

month should hold only the month name, e.g. 'APR' or 'MAY'.

For DBMS's other than SQL Server, you will need to slightly modify the above query.

_______________

  Nayer Naguib
0
 

Author Comment

by:gak2004
ID: 16710747
I am using Oracle
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gak2004
ID: 16710932
Can you please give me the query for oracle
0
 
LVL 4

Expert Comment

by:mbprogrammer
ID: 16711277
The Oracle Query will be something like this :

SELECT  min(To_Date(asof))
FROM scdata
where
   ROUND(TO_DATE (asof),'YEAR')  = ROUND(TO_DATE ('17-MAR-06'),'YEAR')
 and
   ROUND(TO_DATE (asof),'MONTH') = ROUND(TO_DATE ('17-MAR-06'),'MONTH')

0
 
LVL 4

Accepted Solution

by:
mbprogrammer earned 672 total points
ID: 16711312
Hey the follwing query is the same but smaller ;)

SELECT  min(To_Date(asof))
FROM scdata
where
   ROUND(TO_DATE (asof),'MONTH') = ROUND(TO_DATE ('17-MAR-06'),'MONTH')


0
 
LVL 14

Assisted Solution

by:nayernaguib
nayernaguib earned 664 total points
ID: 16717480
For Oracle, use the following query:

SELECT ASOF
FROM SCDATA
WHERE ASOF like '%' || :month || '%'
AND ROWNUM=1
ORDER BY ASOF;

_______________

  Nayer Naguib
0
 
LVL 4

Assisted Solution

by:abd00n
abd00n earned 664 total points
ID: 16739965
Hi,
 mbprogrammer is close to the PROPER solution:
SELECT  min(To_Date(asof))
FROM scdata
where
   TRUNC(TO_DATE (asof),'MONTH') = TRUNC(TO_DATE ('17-MAR-06'),'MONTH')
----

Regards;;
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Starting up a Project

839 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