Problem with date function using SQL Anywhere DBMS 1

[jq65]

I am trying to create a function to return a specific month of the year after passing the function a date argument. I have first created a table as follows:

create table season1 (
season_period CHAR(6) NOT NULL
CHECK (season_period IN ('spring', 'summer', 'autumn', 'winter')),
start_date integer,
end_date integer)!

I executed the following to test that it had been created:

select *
from season1!

All looked ok

The I populated the table by running insert statements one at a time to insert rows:

INSERT INTO season1
values ('spring', 03,05)!
values ('summer',06,08)!
values ('autumn',09,11)!
values ('winter',12,14)!

I again executed the following to test that the table had been populated:

select *
from season1!

All looked ok

I then created the following function using Infomaker's built in date function month(argument date)

CREATE FUNCTION season1 (my_date date)
RETURNS CHAR(6)
BEGIN
DECLARE v_season CHAR(6);
DECLARE v_m_numb integer;
set v_m_numb=MONTH(my_date);
IF v_m_numb = 1 OR v_m_numb = 2
THEN set v_m_numb=v_m_numb + 12;
END IF;
SELECT season_period
INTO v_season
FROM season1
WHERE v_m_numb BETWEEN start_date AND end_date;
RETURN v_season;
END!

No SQLASTATE errors so far and the function has been created because if I repeat I get error saying it already exists


Then when I run the following:

select season1('29/07/1991')!



.......I get "sqlstate 07006cant convert 29/07/1991 to a date/time"


If I try without the quotes:

select season1(29/07/1991)!

I get "SQLSTATE 07006-Cannot convert 0 to a date"

Any clues would be very much appreciated although I know this is a different implementation than what seems to be on this forum - I am sure there must be some similarities - I am new to the SQL game


Regards


JQP65

 

[atyagi]

Try using a date variable instead of passing date as string.

 

[jq65]

atyagi, thanks a lot that worked when I passed it date values from a column - it doesnt work when I passed it values explicitly otherwise

 

[nikhilparchure]

Why dont u use Datepart( mm , @DateVariable)
To get the desired results ....

Regards
Nikhil