1 / 26

Said Salomon Unitrin Direct Insurance timelord@timelordshangout

T-SQL Date and Time Functions Said Salomon. Said Salomon Unitrin Direct Insurance timelord@timelordshangout.com. Who am I?.

ozzy
Download Presentation

Said Salomon Unitrin Direct Insurance timelord@timelordshangout

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. T-SQL Date and Time Functions Said Salomon Said Salomon Unitrin Direct Insurance timelord@timelordshangout.com

  2. Who am I? • I have over 25 year experience IT.  I have a vast array of abilities in the field in the areas of Network, Desktop Support, DBA, Staff Project Management, Application Software Development, Business Analysis and Quality Assurance.   I have Microsoft certifications as MCTS, MCPS, and MCNPS, and multiple certifications from the Insurance Institute of America. Currently I am a DBA at Unitrin Direct Insurance.

  3. DateTime Data type • DateTime • DECLARE @MyDatetimedatetime • January 1, 1753, through December 31, 9999 • 00:00:00 through 23:59:59.997 • Accuracy Rounded to increments of .000, .003, or .007 seconds • Storage size 8 bytes

  4. SmallDateTime Data Type • DECLARE @MySmalldatetimesmalldatetime • January 1, 1900, through June 6, 2079 • 00:00:00 through 23:59:59 • Accuracy One minute • Size 4 bytes, fixed.

  5. DateTime2 Data Type (2008) • DECLARE @MyDatetime2 datetime2(7) • January 1,1 AD through December 31, 9999 AD • 00:00:00 through 23:59:59.9999999 • Accuracy 100 nanoseconds • Storage size 6 bytes for precisions less than 3; 7 bytes for precisions 4 and 5. All other precisions require 8 bytes.

  6. DateTimeOffice Date Type (2008) • DECLARE @MyDatetimeoffsetdatetimeoffset(7) • January 1,1 AD through December 31, 9999 AD • 00:00:00 through 23:59:59.9999999 • Time zone offset range -14:00 through +14:00 • Accuracy 100 nanoseconds • Storage size 10 bytes, fixed is the default with the default of 100ns fractional second precision.

  7. Date Data Type (2008) • DECLARE @MyDatedate • January 1, 1 A.D. through December 31, 9999 A.D. • No time part • Accuracy One day • Storage size 3 bytes, fixed

  8. Time Data Type (2008) • DECLARE @MyTimetime(7) • No Date part • 00:00:00 through 23:59:59.9999999 • Accuracy 100 nanoseconds • Storage size 5 bytes, fixed, is the default with the default of 100ns fractional second precision.

  9. GetDate() Function • GetDate() • Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

  10. DateAddFucntion • DATEADD (datepart , number, date ) • Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

  11. DateDiffFucntion • DATEDIFF ( datepart , startdate , enddate ) • Returns the number of date and time boundaries crossed between two specified dates.

  12. DateNameFucntion • DATENAME ( datepart ,date ) • Returns a character string that represents the specified datepart of the specified date

  13. DatePart Function • DATEPART ( datepart , date ) • Returns an integer that represents the specified datepart of the specified date.

  14. Day Function • Returns an integer representing the day datepart of the specified date.

  15. GetUTCDate Function • GETUTCDATE() • Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

  16. Month Function • MONTH ( date ) • Returns an integer that represents the month part of a specified date.

  17. Year Function • YEAR ( date ) • Returns an integer that represents the year part of a specified date.

  18. SysDateTime Function (2008) • SYSDATETIME () • Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

  19. SysDateTimeOffset Function (2008) • SYSDATETIMEOFFSET () • Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

  20. SysUTCDateTime Function (2008) • SYSUTCDATETIME ( ) • Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

  21. CURRENT_TIMESTAMP (2008) • CURRENT_TIMESTAMP • Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running. • This function is the ANSI SQL equivalent to GETDATE.

  22. SWITCHOFFSET Function (2008) • SWITCHOFFSET (DATETIMEOFFSET,time_zone) • Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

  23. ToDateTimeOffset Function (2008) • TODATETIMEOFFSET (expression,time_zone) • Returns a datetimeoffset value that is translated from a datetime2 expression.

  24. ISDATE Function • ISDATE (expression ) • Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

  25. Cast and Convert • Not really date time functions but they can be useful to format or change date and times

  26. Resources • Date Time Functions Functions (books Online)http://bit.ly/cpcrlN • SQL Server 2008, Dev Edition http://bit.ly/L2hJQ • My TwitterSaidSalomon

More Related