• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

SQL query logic

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
gak2004
Asked:
gak2004
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
nayernaguibCommented:
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
 
gak2004Author Commented:
I am using Oracle
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gak2004Author Commented:
Can you please give me the query for oracle
0
 
mbprogrammerCommented:
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
 
mbprogrammerCommented:
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
 
nayernaguibCommented:
For Oracle, use the following query:

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

_______________

  Nayer Naguib
0
 
abd00nCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now