Shulou(Shulou.com)06/01 Report--

Sybase date function

Date function

Getdate ()

Get the current time, you can set a variety of time formats.

Datepart (date part, date)

Take a certain part of a specified time, year, month, day, minute and second.

Datediff (date part, date 1, date 2)

Calculate 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 certain part of 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

What day is it?

/ / the result of this calculation is + 1, because Sunday is the first day of the week, and Saturday naturally becomes the seventh day of the week.

Set datefirst 1

Select datepart (weekday,getdate ())-weekday

-- string time

Select getdate ()-- '03Universe 11Accord 12'

Select convert (char,getdate (), 101)--'09 Universe 27 Universe 2003'

Select convert (char,getdate (), 102)-'2003.11.12'

Select convert (char,getdate (), 103)-- '27Universe 09Universe 2003'

Select convert (char,getdate (), 104)-'27.09.2003'

Select convert (char,getdate ()) -'27-09-2003'

Select convert (char,getdate (), 106) -'27 Sep 2003'

Select convert (char,getdate (), 107)-- 'Sep 27,2003'

Select convert (char,getdate (), 108)--'11 1615 06'

Select convert (char,getdate (), 109)-- 'Sep 27 2003 11 140 16 28 28 28 AM'

Select convert (char,getdate (), 110) -'09-27-2003'

Select convert (char,getdate (), 111)-- '2003 Universe 09exex27'

Select convert (char,getdate (), 112)-'20030927'

Select rtrim (convert (char,getdate (), 102) +'+ (convert (char,getdate (), 108))-- '2003.11.12 11 char,getdate 03purl 41'

-Integer time

Select convert (int,convert (char (10), getdate (), 112)-20031112

Select datepart (hh,getdate ()) * 10000 + datepart (mi,getdate ()) * 100 + datepart (ss,getdate ())-- 110646

-- conversion of time format "YYYY.MM.DD HH:MI:SS" to "YYYYMMDDHHMISS"

Declare @ a datetime,@tmp varchar (20) @ tmp1 varchar (20)

Select @ a=convert (datetime,'2004.08.03 12 datetime,'2004.08.03 12)

Select @ tmp=convert (char (10), @ aPol 112)

Select @ tmp

Select @ tmp1=convert (char (10), datepart (hh,@a) * 10000 + datepart (mi,@a) * 100 + datepart (ss,@a))

Select @ tmp1

Select @ tmp=@tmp+@tmp1

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 ())-current month

Select @ premm=datepart (month,dateadd (month,-1,getdate ()-Last month

When if (@ mm > = 1 and @ mm=9 and @ mm21), return the date of the day, 21 then convert (varchar,dateadd (dd,0,convert (datetime,getdate () else convert (varchar,dateadd (dd,-1,convert (datetime,getdate () end

1.

Nullif (value1,value2)

Explanation: case when value1=value2 then null

Else value1

End

Example: select nullif (2jue 2)

Return value: null

Select nullif (1 dint 2)

Return value: 1

Note: the data types of value1 and value2 must be compatible

Isnull (value1,value2)

Case when value1=null then value2 else value1 end

Example: select isnull (null,0) = 0

Select isnull (2) 0) = 2

two。 A function about a date

Dateadd (dd,n,dDate)

Explanation: returns the date of n after dDate (n can be negative)

Example: select dateadd (dd,2,'2006-12-10')-- 2 days after the 10th

Return value: Dec 12 2006 12:00AM

Select dateadd (dd,-1,'2006-12-15') = Dec 14 2006 12:00AM

Datediff (day,date1,date2)

Explanation: the number of days between date2 and date1. If date2 comes first, the return value is negative.

Example select datediff (day,'2006-12-10)) =-10

Getdate () returns the current time of the server

Datename (datepart,date) returns the name of the time, for example, select datename (dy,'2006-2-01') = 32 pm.

Round (number,n), rounded for example, select round (123.155) = 123.56

Datepart (day,date) returns the number of days gap on the first day of the month

For example, datepart (day,'2006-12-15') returns 15

Datepart (mm,'2006-12-15') returns 12

Datepart (yy,'2006-12-15') returns 2006

Convert (varchar,datepart (year,@Date1)) + '01calendar / 01'-take the first day of this year

DateAdd (day,- (datepart (day,@Date1)-1), @ Date1)-- take the first day of the month

Dateadd (day,-1,DateAdd (day,- (datepart (day,dateadd (mm,1,@Date1))-1), dateadd (mm,1,@Date1))-- take the last day of the month

Dateadd (yy,-1,DateAdd (day,- (datepart (day,@Date1)-1), @ Date1))-- take the first day of the month last year

Dateadd (day,-1,DateAdd (day,- (day,dateadd (yy,-1,dateadd (mm,1,@Date1))-1), dateadd (yy,-1,dateadd (mm,1,@Date1)-- take the last day of the month last year

The datename () function is equal to datepart

3. Mathematical function

Abs () returns the absolute value of the current number, such as select abs (- 2) = 2

Avg () returns the average of a column

Ceiling returns the smallest integer / * ceiling,n that is greater than or equal to the known value. Ceiling, most * / 2

For example, select ceiling (9.1) = 10 select ceiling (- 9.1)

Count (all | distinct | expression) counts the number of rows

4. String function

Itrim ()

Rtrim () returns the specified expression that deletes the trailing whitespace

Select left ('15425) =' 15'

Select right ('123456century journal 2) =' 56'

Substring ('string',n,m)

Returns a string of m length from the nth.

For example, substring ('iloveyou',2,4) returns' love'

Char_length (?) Returns the length of the string, for example, char_length ('I love you') returns 6. 0. Chinese characters occupy 2 bytes at a time

Char () returns the integer equivalent character.

Count ()

REPLICATE

Repeats the character expression a specified number of times.

Syntax REPLICATE (character_expression, integer_expression), expression 2 can only be a number or a string that can be converted into a number

Example: select Replicate returns 000, and select Replicate returns null.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.