Date Manipulations in Sybase

getdate()
datename(Part, Date)
datepart(RequiredPart, Date)
datediff(PartToCompare, FirstDate, SecondDate)
dateadd(PartToAdd, NumberOfParts, Date)

You can use these functions anywhere BUT if you use them in a WHERE clause you'll generally force a table scan. Much better to store the result of a manipulation if you'll need to search on it regularly.

Date part identifiers...

The following are the date parts recognised by the date functions. Use the abbreviation in the second column to define a date part to a function.

PART            ABBREVIATION PERMITTED RANGE
-------------- ------------ ---------------------
year               yy         1753-9999
quarter            qq         1-4
month              mm         1-12
dayofyear          dy         1-366
day                dd         1-31
week               wk         1-54
weekday            dw         1-7 (1 is Sunday)
hour               hh         0-23
minute             mi         0-59
second             ss         0-59
millisecond        ms         0-999

Date Conversions.

The convert function is used to convert dates to strings. The general format of the command is

convert(datatype [(length)], expression, format)

as, for example...

convert(char(12), invoicedate, 107)

which might result in something like...

'May 24, 1998'

The format codes come in two groups, those with three digits indicating that the full century is to be generated in the conversion...

YEAR ONLY    WITH CENTURY    RESULTING FORMAT
---------    ------------    -----------------------------------
              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:mi:ss
              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