Solved

SQL query logic

Posted on 2006-05-18
704 Views
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
Question by:gak2004

LVL 69

Expert Comment

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

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

I am using Oracle
0

Author Comment

Can you please give me the query for oracle
0

LVL 4

Expert Comment

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

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

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

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

Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …