ࡱ> ~{` bjbjFF 4,,XXXXXXXl....X/lE80N0N0N0N055L6(CEEEEEEEEEEEEE$Fh>IviE9XG645G6G6iEXXN0N0E5@5@5@G6~XN0XN0CE5@G6CE5@5@]BXXBN0,0 Їl.:.uBoCE0E}B,I=,IBB"IXBG6G65@G6G6G6G6G6iEiE@G6G6G6EG6G6G6G6lll#0)lll0)lllXXXXXX DATETIME DATATYPES & FUNCTIONS In Access and in SQL Server all date/time values are stored as a decimal number. The integer part represents the day and the decimal part represents the time as a fraction of a 24-hour day. -657434 -328716 0 1 36600 37130 2958465   Jan Jan Dec Dec Mar Aug Dec 01 01 30 31 15 27 03 100 1000 1899 1899 2000 2001 9999 Note: 37130.00 is midnight on August 27, 2001 37130.25 is 6:00 AM on August 27, 2001 37130.50 is noon August 27, 2001 37130.75 is 6:00 PM on August 27, 2001 Some Conversion Functions in Jet-SQL (ACCESS): CDate(integer expression)returns the date corresponding to an integer value. CLng(date expression) returns the integer for a date CDbl(date/time expression) returns the full decimal number corresponding to the date/time value. SQL Server Datatypes Date/Time Datatypes Used to store Range Amount of storage used NotesDATETIMEDate and time values (stored together)1/1/1753 AD to 12/31/9999 AD8 bytes (4 bytes for the number of days after or before the base date of Jan 1, 1900 and 4 bytes for the number of msec after midnight.)Accuracy to 1/300th sec. (=3.33ms) Values are rounded downward. For example: 4.003 is stored as 4.000 4.006 is stored as 4.003 4.009 is stored as 4.006 Default format is MMM DD YYYY hh:mmAM/PM SMALLDATETIMEDate and time values (stored together)1/1/1900 AD to 6/6/2079 AD4 bytes (2 bytes for the number of days after Jan 1, 1900 and 2 bytes for the number of minutes after midnight.)Accuracy to 1 minute. Built-In Date and Time Functions T-SQL For more information on MS SQL Server Functions go to:  HYPERLINK "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_9dyr.asp" http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_9dyr.asp DATEADD Returns a new datetime value based on adding a specified number of specified intervals to the specified date. Syntax DATEADD (datepart , number, date ) Arguments datepart Is the parameter that specifies on which part of the date to return a new value. This table lists the dateparts and abbreviations recognized by Microsoft SQL Server"!. DatepartAbbreviationsyearyy, yyyyquarterqq, qmonthmm, mdayofyeardy, ydaydd, dweekwk, wwhourhhminutemi, nsecondss, smillisecondms number is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and1.75 for number, date is incremented by 1. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format. When entering datetime values, always enclose them in quotation marks. If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years. Return Types Returns datetime, or smalldatetime if the date argument is smalldatetime. DATEDIFF Returns the number of date and time boundaries crossed between two specified dates. Syntax DATEDIFF (datepart , startdate , enddate ) Arguments datepart is same as for DATEADD startdate is the beginning date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0. When entering datetime values, always enclose them in quotation marks. enddate is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Return Types integer Remarks startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned. DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years. The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1. DATEPART Returns an integer representing the specified datepart of the specified date. Syntax DATEPART (datepart , date ) Arguments datepart is same as for DATEADD and has additional option of weekdaydw The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year. The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0. Return Types int Remarks The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively. DATENAME Returns a character string representing the specified datepart of the specified date. Syntax DATENAME ( datepart , date ) Arguments datepart is same as for DATEPART date is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type for dates after January 1, 1753. Store as character data for earlier dates. Return Types nvarchar Remarks SQL Server automatically converts between character and datetime values as necessary, for example, when you compare a character value with a datetime value. DAY Returns an integer representing the day datepart of the specified date. Syntax DAY ( date ) Arguments date is an expression of type datetime or smalldatetime. Return Type int Remarks This function is equivalent to DATEPART(dd, date). MONTH Returns an integer that represents the month part of a specified date. Syntax MONTH ( date ) Arguments date is an expression returning a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Return Types int Remarks MONTH is equivalent to DATEPART(mm, date). YEAR Returns an integer that represents the year part of a specified date. Syntax YEAR ( date ) Arguments date An expression of type datetime or smalldatetime. Return Types int Remarks This function is equivalent to DATEPART(yy, date). GETDATE Returns the current system       " $ 0 3 Q Z ^ _ w y z } ź||tttk_Vk_k_Vk_hcAS5CJaJhcAShp 5CJaJhu5CJaJhcASCJaJ(jhp hp CJUaJmHnHuhp hp 5CJ\aJhuCJaJhp h=CJaJhp CJaJhp hp CJaJhp hp CJ\aJhMxhp 5CJ$\aJ$hDT5CJ$\aJ$h5 5CJ$\aJ$hu5CJ$\aJ$!    ^   3 [ | % & gdu^gdp gdp `gdu^gdcASgdp $a$gdp    A E G H Z g i { ; Ƚֵֵֵֵ֟yoddhp huCJaJhihu5\h5 5CJ\aJhu5CJ\aJhuCJaJhp CJaJh'h'CJaJhp h'CJaJh'CJaJh'5CJ\aJhp hp 5CJ\aJhp hp CJaJhcAS5CJaJhcAShp 5CJaJhu5CJaJ#; < D \  "   oǿׅymydXI>hd[zh,#CJaJjhd[zh,#CJUaJhd[zh,#5CJaJh,#5CJaJh?~hu5CJ0aJ0h?~h`5CJ0aJ0h?~hCKH5CJ0aJ0hp hp CJH*aJhp hp CJaJhp hp 5CJaJhp h=CJaJhih=5hih|_5h=CJaJhp CJaJhp huCJaJhuCJaJhp hu5CJ\aJ& [ \     ! $$Ifa$gdp $Ifgdp gd|_^gdp  >^`>gdugdu! " + A S \ ` a KBBBBBB $Ifgdp kd$$Ifl.r,|)t$  t044 laa e f t | ! ? @ M f  $Ifgdp    MDDDDDD $Ifgdp kd$$Iflr,|)t$  t044 la $ $Ifgdp MHH@88$a$gd,#$a$gd=gdp kd$$Iflr,|)t$  t044 laopq FVXƻ}}ujVVVuj&hp h`6CJOJQJ]^JaJhp h`CJaJh?~CJaJh?~CJOJQJ^JaJ&hp h`5CJOJQJ\^JaJ hp h`CJOJQJ^JaJh?~h`CJ aJ h?~h?~CJ aJ hd[zh,#CJaJhd[zh,#0JCJaJjhd[zh,#CJUaJ#jwhd[zh,#CJUaJ\n $Ifgd?~ dgd?~gd?~ dgd?~gd?~  XZ\ʹtctNtNctNtNctNtNctNtN)h?~h?~B*CJOJQJ^JaJph h?~h?~CJOJQJ^JaJ/h?~h?~5B*CJOJQJ\^JaJphh`CJOJQJ^JaJh?~CJOJQJ^JaJ hp h`CJOJQJ^JaJ hp h%]CJOJQJ^JaJ h`6CJOJQJ]^JaJ&hp h`6CJOJQJ]^JaJ h?~6CJOJQJ]^JaJvmm $Ifgd?~kdX$$If0s*! 0u!634abpvmm $Ifgd?~kd$$If0s*! 0u!634abpvmm $Ifgd?~kd$$If0s*! 0u!634abpvmm $Ifgd?~kd$$If0s*! 0u!634abp vmm $Ifgd?~kdd$$If0s*! 0u!634abp  "$024<>BDFRT^`bnpz|~z}ﱝ~j&hp h`5CJOJQJ\^JaJh?~CJOJQJ^JaJ hp h`CJOJQJ^JaJ&hp h`6CJOJQJ]^JaJ h?~6CJOJQJ]^JaJ)h?~h?~B*CJOJQJ^JaJph/h?~h?~5B*CJOJQJ\^JaJph h?~h?~CJOJQJ^JaJ)$2vmm $Ifgd?~kd'$$If0s*! 0u!634abp24>Dvmm $Ifgd?~kd$$If0s*! 0u!634abpDFT`vmm $Ifgd?~kd $$If0s*! 0u!634abp`bp|vmm $Ifgd?~kdp $$If0s*! 0u!634abp|~vmm $Ifgd?~kd3 $$If0s*! 0u!634abpqrWvjj^jj^^^ dgd?~ dgd?~kd $$If0s*! 0u!634abp '7?qrWXemuwyz{p{hhWRCJaJhp h`CJaJh?~CJaJ&hp h,jO5CJOJQJ\^JaJ hp h,jOCJOJQJ^JaJh?~CJOJQJ^JaJ h?~6CJOJQJ]^JaJ&hp h`6CJOJQJ]^JaJ&hp h`5CJOJQJ\^JaJ hp h`CJOJQJ^JaJ(WXeFGQZrs} dgd?~gd?~gd?~ dgd?~gd?~#$-./9:;CEFGQYZ]eqrs|}~$1Yf佩䩽䩽ѩљшzi hp h CJOJQJ^JaJh-ZCJOJQJ^JaJ h-Z6CJOJQJ]^JaJh(CJaJh-ZCJaJ&hp h`6CJOJQJ]^JaJ&hp h`5CJOJQJ\^JaJhp h`CJaJhkCJaJ hp h`CJOJQJ^JaJh?~h`CJ aJ (?GKXUVʶ{p{pahSC5CJ OJQJ^JaJ hp h`CJaJh-ZCJaJ&hp h`5CJOJQJ\^JaJh-ZCJOJQJ^JaJ hp h`CJOJQJ^JaJ&hp h`6CJOJQJ]^JaJ h-Z6CJOJQJ]^JaJ&hp h,jO5CJOJQJ\^JaJ hp h,jOCJOJQJ^JaJ#TUWXa%( $Ifgd8hgd?~gd?~ dgd?~VXa$źͦ͒źźwbJ/h?~h8h5B*CJOJQJ\^JaJph)h8h5B*CJOJQJ\^JaJphhp h8hCJaJh8hCJaJh/CJaJ&hp h`6CJOJQJ]^JaJ&hp h`5CJOJQJ\^JaJhp h`CJaJh-ZCJaJ hp h`CJOJQJ^JaJ#hh`5CJ OJQJ^JaJ hWR5CJ OJQJ^JaJ $%')*.24:   # ' 4 l t %!2!Z!g!!Ƕoah-ZCJOJQJ^JaJ&hp h`6CJOJQJ]^JaJ h-Z6CJOJQJ]^JaJh8hCJOJQJ^JaJ&hp h`5CJOJQJ\^JaJ hp h`CJOJQJ^JaJhp h`CJaJ/h?~h8h5B*CJOJQJ\^JaJph)h?~h8hB*CJOJQJ^JaJph&()*!!vqeeeYYePgd?~ dgd?~ dgd?~gd?~kd $$If0s*! 0u!634abp !!!!!!!!!!! " """""$"&"*":";"<"=">"@"I""""""ȴȴȴȣwldP&hp h%]5CJOJQJ\^JaJh]yCJaJhh%]CJ aJ hWRCJaJhp h%]CJaJ hp h?~CJOJQJ^JaJh?~CJaJ hp h%]CJOJQJ^JaJ&hp h`6CJOJQJ]^JaJ hp h`CJOJQJ^JaJ&hp h`5CJOJQJ\^JaJhp h`CJaJh-ZCJaJ!!!!!;"<"="?"@"I"""""""""""## dgd?~gd?~ dgd?~gd?~ dgd?~gd?~""""""""""""""""""""####0#h#p######## $($u$}$$$$$ǿǿDžǿǿ}ujhh`CJ aJ hWRCJ aJ hCJ aJ h]yCJOJQJ^JaJ h]y6CJOJQJ]^JaJhcCJaJhJCJaJhp h%]CJaJh]yCJaJ hp h%]CJOJQJ^JaJ&hp h%]5CJOJQJ\^JaJ&hp h%]6CJOJQJ]^JaJ(#####$$$$$$$$$$$$/%0%<%@%A%I%|%%% dgd?~gd?~ dgd?~gd?~$$$$$$$$$$$$$$%% %-%/%0%<%?%@%A%I%q%s%u%y%|%}%%%%%%%%%%%%%%%&&&(&`&ȴܴȴܴ}h CJOJQJ^JaJh CJaJhh`CJ aJ h?~CJaJh]yCJOJQJ^JaJ&hp h`6CJOJQJ]^JaJ&hp h`5CJOJQJ\^JaJhp h`CJaJh]yCJaJ hp h`CJOJQJ^JaJ1%%%%%%%&&&&&&&&&&&&&/'0'7'F'G'gdSCgd?~ dgd?~ dgd?~gd?~`&h&&&&&&&&&&&&&&&/'0'7'<'>'C'E'F'G'Q'U'l't'x'''''''''''''''''''''۴۬ȴ۴ۏh?~CJaJ hp h`hh`CJ aJ hSChSChSChCJ aJ &hp h`6CJOJQJ]^JaJhp h`CJaJh CJaJ hp h`CJOJQJ^JaJ&hp h`5CJOJQJ\^JaJ1G'Q'V''''''''''''''''~~~~~~gdSCgd?~gd?~ dgd?~ dgd?~gd?~''(~~~~~~~~~~~  ƻƻhp h`CJaJh?~5CJ\aJhp h?~5CJ\aJhp h?~CJaJh CJaJ&hp h?~5CJOJQJ\^JaJU hp h?~CJOJQJ^JaJhh?~CJ aJ date and time in the Microsoft SQL Server"! standard internal format for datetime values. Syntax GETDATE ( ) Return Types datetime 21h:p?~/ =!`"`#`$`% $$If!vh5t5$ 555 #vt#v$ #v#v#v :V l. t05t5$ 555 $$If!vh5t5$ 555 #vt#v$ #v#v#v :V l t05t5$ 555 $$If!vh5t5$ 555 #vt#v$ #v#v#v :V l t05t5$ 555 DyK [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_9dyr.aspyK http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_9dyr.asp$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34p$$If!vh55#v#v:V 0u!655 / 34pL@L `Normal$CJOJQJ^J_HaJmH sH tH :@: ` Heading 1$@&5\@@@ ` Heading 2$@&`>*N@N ` Heading 5 <@&56CJ\]aJDA@D Default Paragraph FontRi@R  Table Normal4 l4a (k@(No List6U@6 ` Hyperlink >*B*phX^@X ` Normal (Web)dhxB*OJQJ^JphLOL `indentdhxB*OJQJ^Jph ^3[|%&[\!"+AS\`aeft| !?@Mf  $DELpq{. 7 E F K T U ] c d j p q { q r W X e FGQZrs}TUWXa%()*;<=?@I/0<@AI|/07FGQVZ[bop}000000000000000000000000000000000 00 00 00 00 0 0 00 000000 00 000000000000 0 0 00 0000000 00 0 0 00000000H0H00H0H0000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000H0H00000000H0H00H0H000000000H0H00H0H00000000000H0H00H0H0000 0 0 0000000H0H00H0H00000000H0H00H0H000000H0H00H0H0000000H0H00H0H000H0H00H0H0000H0H00H0H000H0H00H0H000000000H0H00H0H000H0H00H0H0000000000@0H0H0@0H0H007 E F K T U ] c d j p q { %()=Zbo}{00{00{00 {00{00{0 0{00{00{0 0{00{00@0<{00{00@@{0 0{0 0@D{0 0{0 0J@0{00{00@0{00{00@0{00{00@0{00{00@<0PH@P{0!0 "{0!0 {0!0 {00{00 @0 0{0%0&]{0$0{0$0{0$0 0 ; oV$!"$`&'!'.013468:<>& ! a 2D`|W(!#%G' "#$%&()*+,-/2579;=pX8  @n  (  NB  S DNB @ S DHB  C DHB  C DHB  C DHB   C DHB   C DHB   C DHB   C DB S  ? ''t : : t t ttt00tnNtNS*t _date_and_time_functions_dateadd _datediff _datename _datepart_day_getdate_month_year U|| U||&*\`U]{. 6 K M O S ] _ q z { } 7 ? m u z $,/8;BQYs|$1Yf?GKX%'8:<D  #'4lt%2Zg"$#0hp (u} -<?qs(`hltxIQ}&+\aU_{ ) o m v $.QYZ\s|}a  I<?hq.<BQUVY}333333333333333333333333333333333333333333333333333333333333333333 ^|St. GQZsXa$%*@I0<AI|07GQp}"vh^`.h  ^ `.h L ^ `L.hxx^x`.hHH^H`.hL^`L.h^`.h^`.hL^`L."         Y+ {(6Y+ #"=p %],#sBSCCKH,jOcASDT-Z|_8hMx]y'?~ck45 J (WRCiu`/w!"+St. 7 E F K T U ] c d j p q { %()@h/4 P@PP(@P~UnknownGz Times New Roman5Symbol3& z Arial7&  Verdana"1hSbډ&c"?77#``4dxx2QHX ?`2Date and Time FunctionsUSER ENGINEERING Oh+'0 ( H T ` lxDate and Time FunctionsUSER Normal.dot ENGINEERING34Microsoft Office Word@:@@2@&[՜.+,D՜.+,` hp   City College of San Francisco7xG Date and Time Functions Title8 8@ _PID_HLINKSA*'[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_9dyr.aspJ  !"#$%&'()*+,-./0123456789:;<=>?@BCDEFGHJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmopqrstuwxyz{|}Root Entry FlData A1TableIIWordDocument4SummaryInformation(nDocumentSummaryInformation8vCompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q