Home > Articles > Web Development

📄 Contents

  1. Sams Teach Yourself SQL in 24 Hours, Third Edition
  2. Table of Contents
  3. Copyright
  4. About the Authors
  5. Acknowledgments
  6. Tell Us What You Think!
  7. Introduction
  8. Part I: A SQL Concepts Overview
  9. Hour 1. Welcome to the World of SQL
  10. SQL Definition and History
  11. SQL Sessions
  12. Types of SQL Commands
  13. An Introduction to the Database Used in This Book
  14. Summary
  15. Q&A
  16. Workshop
  17. Part II: Building Your Database
  18. Hour 2. Defining Data Structures
  19. What Is Data?
  20. Basic Data Types
  21. Summary
  22. Q&A
  23. Workshop
  24. Hour 3. Managing Database Objects
  25. What Are Database Objects?
  26. What Is a Schema?
  27. A Table: The Primary Storage for Data
  28. Integrity Constraints
  29. Summary
  30. Q&A
  31. Workshop
  32. Hour 4. The Normalization Process
  33. Normalizing a Database
  34. Summary
  35. Q&A
  36. Workshop
  37. Hour 5. Manipulating Data
  38. Overview of Data Manipulation
  39. Populating Tables with New Data
  40. Updating Existing Data
  41. Deleting Data from Tables
  42. Summary
  43. Q&A
  44. Workshop
  45. Hour 6. Managing Database Transactions
  46. What Is a Transaction?
  47. What Is Transactional Control?
  48. Transactional Control and Database Performance
  49. Summary
  50. Q&A
  51. Workshop
  52. Part III: Getting Effective Results from Queries
  53. Hour 7. Introduction to the Database Query
  54. What Is a Query?
  55. Introduction to the <tt>SELECT</tt> Statement
  56. Examples of Simple Queries
  57. Summary
  58. Q&amp;A
  59. Workshop
  60. Hour 8. Using Operators to Categorize Data
  61. What Is an Operator in SQL?
  62. Comparison Operators
  63. Logical Operators
  64. Conjunctive Operators
  65. Negating Conditions with the <tt>NOT</tt> Operator
  66. Arithmetic Operators
  67. Summary
  68. Q&amp;A
  69. Workshop
  70. Hour 9. Summarizing Data Results from a Query
  71. What Are Aggregate Functions?
  72. Summary
  73. Q&amp;A
  74. Workshop
  75. Hour 10. Sorting and Grouping Data
  76. Why Group Data?
  77. The <tt>GROUP BY</tt> Clause
  78. <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
  79. The <tt>HAVING</tt> Clause
  80. Summary
  81. Q&amp;A
  82. Workshop
  83. Hour 11. Restructuring the Appearance of Data
  84. The Concepts of ANSI Character Functions
  85. Various Common Character Functions
  86. Miscellaneous Character Functions
  87. Mathematical Functions
  88. Conversion Functions
  89. The Concept of Combining Character Functions
  90. Summary
  91. Q&amp;A
  92. Workshop
  93. Hour 12. Understanding Dates and Times
  94. How Is a Date Stored?
  95. Date Functions
  96. Date Conversions
  97. Summary
  98. Q&amp;A
  99. Workshop
  100. Part IV: Building Sophisticated Database Queries
  101. Hour 13. Joining Tables in Queries
  102. Selecting Data from Multiple Tables
  103. Types of Joins
  104. Join Considerations
  105. Summary
  106. Q&amp;A
  107. Workshop
  108. Hour 14. Using Subqueries to Define Unknown Data
  109. What Is a Subquery?
  110. Embedding a Subquery Within a Subquery
  111. Summary
  112. Q&A
  113. Workshop
  114. Hour 15. Combining Multiple Queries into One
  115. Single Queries Versus Compound Queries
  116. Why Would I Ever Want to Use a Compound Query?
  117. Compound Query Operators
  118. Using an <tt>ORDER BY</tt> with a Compound Query
  119. Using <tt>GROUP BY</tt> with a Compound Query
  120. Retrieving Accurate Data
  121. Summary
  122. Workshop
  123. Q&amp;A
  124. Part V: SQL Performance Tuning
  125. Hour 16. Using Indexes to Improve Performance
  126. What Is an Index?
  127. How Do Indexes Work?
  128. The <tt>CREATE INDEX</tt> Command
  129. Types of Indexes
  130. When Should Indexes Be Considered?
  131. When Should Indexes Be Avoided?
  132. Summary
  133. Q&amp;A
  134. Workshop
  135. Hour 17. Improving Database Performance
  136. What Is SQL Statement Tuning?
  137. Database Tuning Versus SQL Tuning
  138. Formatting Your SQL Statement
  139. Full Table Scans
  140. Other Performance Considerations
  141. Performance Tools
  142. Summary
  143. Q&amp;A
  144. Workshop
  145. Part VI: Using SQL to Manage Users and Security
  146. Hour 18. Managing Database Users
  147. Users Are the Reason
  148. The Management Process
  149. Tools Utilized by Database Users
  150. Summary
  151. Q&amp;A
  152. Workshop
  153. Hour 19. Managing Database Security
  154. What Is Database Security?
  155. How Does Security Differ from User Management?
  156. What Are Privileges?
  157. Controlling User Access
  158. Controlling Privileges Through Roles
  159. Summary
  160. Q&amp;A
  161. Workshop
  162. Part VII: Summarized Data Structures
  163. Hour 20. Creating and Using Views and Synonyms
  164. What Is a View?
  165. Creating Views
  166. Dropping a View
  167. What Is a Synonym?
  168. Summary
  169. Q&amp;A
  170. Workshop
  171. Hour 21. Working with the System Catalog
  172. What Is the System Catalog?
  173. How Is the System Catalog Created?
  174. What Is Contained in the System Catalog?
  175. Examples of System Catalog Tables by Implementation
  176. Querying the System Catalog
  177. Updating System Catalog Objects
  178. Summary
  179. Q&amp;A
  180. Workshop
  181. Part VIII: Applying SQL Fundamentals in Today's World
  182. Hour 22. Advanced SQL Topics
  183. Advanced Topics
  184. Cursors
  185. Stored Procedures and Functions
  186. Triggers
  187. Dynamic SQL
  188. Call-Level Interface
  189. Using SQL to Generate SQL
  190. Direct Versus Embedded SQL
  191. Summary
  192. Q&amp;A
  193. Workshop
  194. Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  195. SQL and the Enterprise
  196. Accessing a Remote Database
  197. Accessing a Remote Database Through a Web Interface
  198. SQL and the Internet
  199. SQL and the Intranet
  200. Summary
  201. Q&amp;A
  202. Workshop
  203. Hour 24. Extensions to Standard SQL
  204. Various Implementations
  205. Examples of Extensions from Some Implementations
  206. Interactive SQL Statements
  207. Summary
  208. Q&amp;A
  209. Workshop
  210. Part IX: Appendixes
  211. Appendix A. Common SQL Commands
  212. SQL Statements
  213. SQL Clauses
  214. Appendix B. Using MySQL for Exercises
  215. Windows Installation Instructions
  216. Linux Installation Instructions
  217. Appendix C. Answers to Quizzes and Exercises
  218. Hour 1, "Welcome to the World of SQL"
  219. Hour 2, "Defining Data Structures"
  220. Hour 3, "Managing Database Objects"
  221. Hour 4, "The Normalization Process"
  222. Hour 5, "Manipulating Data"
  223. Hour 6, "Managing Database Transactions"
  224. Hour 7, "Introduction to the Database Query"
  225. Hour 8, "Using Operators to Categorize Data"
  226. Hour 9, "Summarizing Data Results from a Query"
  227. Hour 10, "Sorting and Grouping Data"
  228. Hour 11, "Restructuring the Appearance of Data"
  229. Hour 12, "Understanding Dates and Time"
  230. Hour 13, "Joining Tables in Queries"
  231. Hour 14, "Using Subqueries to Define Unknown Data"
  232. Hour 15, "Combining Multiple Queries into One"
  233. Hour 16, "Using Indexes to Improve Performance"
  234. Hour 17, "Improving Database Performance"
  235. Hour 18, "Managing Database Users"
  236. Hour 19, "Managing Database Security"
  237. Hour 20, "Creating and Using Views and Synonyms"
  238. Hour 21, "Working with the System Catalog"
  239. Hour 22, "Advanced SQL Topics"
  240. Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
  241. Hour 24, "Extensions to Standard SQL"
  242. Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
  243. <tt>EMPLOYEE_TBL</tt>
  244. <tt>EMPLOYEE_PAY_TBL</tt>
  245. <tt>CUSTOMER_TBL</tt>
  246. <tt>ORDERS_TBL</tt>
  247. <tt>PRODUCTS_TBL</tt>
  248. Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
  249. <tt>INSERT</tt> Statements
  250. Appendix F. Glossary
  251. Appendix G. Bonus Exercises
  • Print
  • + Share This
  • 💬 Discuss
Recommended Book

Date Functions

Date functions are available in SQL depending on the options with each specific implementation. Date functions, similar to character string functions, are used to manipulate the representation of date and time data. Available date functions are often used to format the output of dates and time in an appealing format, compare date values with one another, compute intervals between dates, and so on.

The Current Date

You may have already raised the question: How do I get the current date from the database? The need to retrieve the current date from the database may originate from several situations, but the current date is normally returned either to compare to a stored date or to return the value of the current date as some sort of timestamp.

newterm_icon.gif

The current date is ultimately stored on the host computer for the database, and is called the system date. The database, which interfaces with the appropriate operating system, has the capability to retrieve the system date for its own purpose or to resolve database requests, such as queries.

Take a look at a couple of methods of attaining the system date based on commands from two different implementations.

Sybase uses a function called GETDATE() to return the system date. This function is used in a query as follows. The output is what would return if today's current date were New Year's Eve for 1999.

   mysql_icon.gif
   oracle_icon.gif
   input_icon.gif

   SELECT GETDATE()

   output_icon.gif
Dec 31, 1999

Oracle uses what is calls a pseudocolumn, SYSDATE, to retrieve the current date. SYSDATE acts as any other column in a table and can be selected from any table in the database, although it is not actually part of the table's definition.

To return the system date in Oracle, the following statement returns the output if today were New Year's Eve before 2002:

   mysql_icon.gif
   input_icon.gif

   SELECT SYSDATE FROM TABLE_NAME

   output_icon.gif
31-DEC-01

Time Zones

The use of time zones may be a factor when dealing with date and time information. For instance, a time of 6:00 p.m. in central United States does not equate to the same time in Australia, although the actual point in time is the same. Some of us who live within the daylight saving time zone are used to adjusting our clocks twice a year. If time zones are considerations when maintaining data in your case, you may find it necessary to consider time zones and perform time conversions, if available with your SQL implementation.

The following are some common time zones and their abbreviations:

Abbreviation

Definition

AST, ADT

Atlantic standard, daylight time

BST, BDT

Bering standard, daylight time

CST, CDT

Central standard, daylight time

EST, EDT

Eastern standard, daylight time

GMT

Greenwich mean time

HST, HDT

Alaska/Hawaii standard, daylight time

MST, MDT

Mountain standard, daylight time

NST

Newfoundland standard, daylight time

PST, PDT

Pacific standard, daylight time

YST, YDT

Yukon standard, daylight time

The following table shows examples of time zone differences based on a given time:

Time Zone

Time

AST

June 12th, 2002 at 1:15 PM

BST

June 12th, 2002 at 6:15 AM

CST

June 12th, 2002 at 11:15 AM

EST

June 12th, 2002 at 12:15 PM

GMT

June 12th, 2002 at 5:15 PM

HST

June 12th, 2002 at 7:15 AM

MST

June 12th, 2002 at 10:15 AM

NST

June 12th, 2002 at 1:45 PM

PST

June 12th, 2002 at 9:15 AM

YST

June 12th, 2002 at 8:15 AM

Adding Time to Dates

Days, months, and other parts of time can be added to dates for the purpose of comparing dates to one another, or to provide more specific conditions in the WHERE clause of a query.

Intervals can be used to add periods of time to a DATETIME value. As defined by the standard, intervals are used to manipulate the value of a DATETIME value, as in the following examples:

   mysql_icon.gif
   input_icon.gif

   DATE '1999-12-31' + INTERVAL '1' DAY

   output_icon.gif
'2000-01-01'
input_icon.gif

   DATE '1999-12-31' + INTERVAL '1' MONTH

   output_icon.gif
'2000-01-31'

The following is an example using the SQL Server function DATEADD:

   input_icon.gif

   SELECT DATEADD(MONTH, 1, DATE_HIRE)

   FROM EMPLOYEE_PAY_TBL;

   output_icon.gif
DATE_HIRE ADD_MONTH
--------- ---------
23-MAY-89 23-JUN-89
17-JUN-90 17-JUL-90
14-AUG-94 14-SEP-94
28-JUN-97 28-JUL-97
22-JUL-96 22-AUG-96
14-JAN-91 14-FEB-91

6 rows affected.

The following example uses the Oracle function ADD_MONTHS:

   mysql_icon.gif
   input_icon.gif

   SELECT DATE_HIRE, ADD_MONTHS(DATE_HIRE,1)

   FROM EMPLOYEE_PAY_TBL;

   output_icon.gif
DATE_HIRE ADD_MONTH
--------- ---------
23-MAY-89 23-JUN-89
17-JUN-90 17-JUL-90
14-AUG-94 14-SEP-94
28-JUN-97 28-JUL-97
22-JUL-96 22-AUG-96
14-JAN-91 14-FEB-91

6 rows selected.

To add one day to a date in Oracle, use the following:

   input_icon.gif

   SELECT DATE_HIRE, DATE_HIRE + 1

   FROM EMPLOYEE_PAY_TBL

   WHERE EMP_ID = '311549902';

   output_icon.gif
DATE_HIRE DATE_HIRE
--------- ---------
23-MAY-89 24-MAY-89

1 row selected.

Notice that these examples in SQL Server and Oracle, although they differ syntactically from the ANSI examples, derive their results based on the same concept as described by the SQL standard.

Comparing Dates and Time Periods

OVERLAPS is a powerful standard SQL conditional operator for DATETIME values. The OVERLAPS operator is used to compare two timeframes and return the Boolean value TRUE or FALSE, depending on whether the two timeframes overlap. The following comparison returns the value TRUE:

(TIME '01:00:00' , TIME '05:59:00') 
OVERLAPS
(TIME '05:00:00' , TIME '07:00:00')

The following comparison returns the value FALSE:

(TIME '01:00:00' , TIME '05:59:00') 
OVERLAPS
(TIME '06:00:00 , TIME '07:00:00')

Miscellaneous Date Functions

The following list shows some powerful date functions that exist in the implementations for SQL Server, Oracle, and MySQL.

SQL Server

 

DATEPART

Returns the integer value of a DATEPART for a date

DATENAME

Returns the text value of a DATEPART for a date

GETDATE()

Returns the system date

DATEDIFF

Returns the difference between two dates for specified date parts, such as days, minutes, and seconds

Oracle

 

NEXT_DAY

Returns the next day of the week as specified (for example, FRIDAY) since a given date

MONTHS_BETWEEN

Returns the number of months between two given dates

MySQL

 

DAYNAME(date)

Displays day of week

DAYOFMONTH(date)

Displays day of month

DAYOFWEEK(date)

Displays day of week

DAYOFYEAR(date)

Displays day of year

  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus