Help with Date Formatting 1

[Toga]

I'm trying to write a stored procedure that returns to my application the next sequential number....in a number of different formats, depending upon what I want for a given table. The last sequential number returned is stored in a table on the server.

I also want to have several variations of ID #'s available to my app such as one using the date in it like 01-081002.

I want the flexibility to return the date in several formats such as mmddyy, ddmmyy, mmddyyy, etc.

I've been trying to use the following statement

convert(varchar,getdate( ),convert(int,date_format)

where date_format = mmddyy

I keep getting the following error: Syntax error during explicit conversion of VARCHAR value 'mmddyy' to a INT field.

Can anyone help please? Can this even be done in Sybase?

 

[grega]

As with anything, there are a number of ways to do it. Here's a snippet of SQL which may help;

declare @fulldate char(6)
declare @year char(2)
declare @month char(2)
declare @day char(2)

select @fulldate=convert(char,getdate(),12)
select @year=substring(@fulldate,1,2)
select @month=substring(@fulldate,3,2)
select @day=substring(@fulldate,5,2)

/* return today's date in ddmmyy format
just change the select to change the format */

select @day + @month + @year

When you use the convert function to format a datetime value (as we do above with getdate()), the 3rd parameter specifies the format of that date. The full list is;

Without   With        Output
Century   Century
N/A       0 or 100    mon dd yyyy hh:miAM (or PM)
1         101         mm/dd/yy
2         102         yy.mm.dd
3         103         dd/mm/yy
4         104         dd.mm.yy
5         105         dd-mm-yy
6         106         dd mon yy
7         107         mon dd, yy
8         108         hh:mm:ss
N/A       9 or 109    mon dd yyyy hh:mi:ss:mmmAM (or PM)
10        110         mm-dd-yy
11        111         yy/mm/dd
12        112         yymmdd

Greg.