Sybase Date function
Date function
GETDATE ()
Get the current time, can be set to get a variety of time formats.
DATEPART (date part, date)
Take a certain part of the specified time, month and day seconds.
DateDiff (date part, date 1, date 2)
Calculates the time difference between the specified date 1 and date 2.
DATEADD (date part, numeric expression, date)
Calculates the specified time, plus the length of time specified by the expression.
-Take a part of the time
Select DATEPART (yy,getdate ())--year
Select DATEPART (mm,getdate ())--month
Select DATEPART (dd,getdate ())--day
Select DATEPART (hh,getdate ())--hour
Select DATEPART (mi,getdate ())--min
Select DATEPART (ss,getdate ())--sec
--Take the day of the week
Set Datefirst 1
Select DATEPART (weekday,getdate ())--weekday
--String time
Select GETDATE ()--' 03/11/12 '
Select CONVERT (Char,getdate (), 101)--' 09/27/2003 '
Select CONVERT (Char,getdate (), 102)--' 2003.11.12 '
Select CONVERT (Char,getdate (), 103)--' 27/09/2003 '
Select CONVERT (Char,getdate (), 104)--' 27.09.2003 '
Select CONVERT (Char,getdate (), 105)--' 27-09-2003 '
Select CONVERT (Char,getdate (), 106)-' SEP 2003 '
Select CONVERT (Char,getdate (), 107)--' Sep 27, 2003 '
Select CONVERT (Char,getdate (), 108)--' 11:16:06 '
Select CONVERT (Char,getdate (), 109)--' SEP 2003 11:16:28:746am '
Select CONVERT (Char,getdate (), 110)--' 09-27-2003 '
Select CONVERT (Char,getdate (), 111)--' 2003/09/27 '
Select CONVERT (Char,getdate (), 112)--' 20030927 '
Select RTrim (CONVERT (Char,getdate (), 102)) + "+ (CONVERT (Char,getdate (), 108))--' 2003.11.12 11:03:41 '
--Get current time
Select CONVERT (Time,getdate ())--09:19:23.231
--Integer Time
Select CONVERT (Int,convert (char), GETDATE (), 112))--20031112
Select DATEPART (hh,getdate ()) *10000 + datepart (mi,getdate ()) *100 + datepart (ss,getdate ())--110646
--Time format "YYYY. Mm. DD HH:MI:SS "converted to" Yyyymmddhhmiss "
DECLARE @a datetime, @tmp varchar, @tmp1 varchar (20)
Select @a=convert (DateTime, ' 2004.08.03 12:12:12 ')
Select @tmp =convert (char), @a,112
Select @tmp
Select @tmp1 =convert (char), DATEPART (hh,@a) *10000 + datepart (mi,@a) *100 + datepart (ss,@a))
Select @tmp1
Select @[email Protected][email protected]
Select @tmp
--the last day of the month
Declare
@tmpstr varchar (10)
@mm int,
@premm int,
@curmmlastday varchar (10)
Begin
Select @mm =datepart (Month,getdate ())--month
Select @premm =datepart (Month,dateadd (Month,-1,getdate ()))--Last month
if (@mm >=1 and @mm <=8)
Select @tmpstr =convert (char (4), DatePart (Year,getdate ())) + '. 0 ' +convert (char (1), DatePart (Month,dateadd (month,1), GETDATE ())) + '. ' + ' 01 '
else if (@mm >=9 and @mm <=11)
Select @tmpstr =convert (char (4), DatePart (Year,getdate ())) + '. ' +convert (char (2), DatePart (Month,dateadd (Month,1,getdate ())) + '. ' + ' 01 '
Else
Select @tmpstr =convert (char (4), DatePart (Year,dateadd (Year,1,getdate ())) + '. 0 ' +convert (char (1), DATEPART (month, DATEADD (Month,1,getdate ())) + '. ' + ' 01 '
Select @curmmlastday =convert (char), DATEADD (Day,-1, @tmpstr), 102)--Last day of the month
End
Sybase common time-date function