- Sams Teach Yourself SQL in 24 Hours, Third Edition
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- SQL Definition and History
- SQL Sessions
- Types of SQL Commands
- An Introduction to the Database Used in This Book
- Summary
- Q&A
- Workshop
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- What Is Data?
- Basic Data Types
- Summary
- Q&A
- Workshop
- Hour 3. Managing Database Objects
- What Are Database Objects?
- What Is a Schema?
- A Table: The Primary Storage for Data
- Integrity Constraints
- Summary
- Q&A
- Workshop
- Hour 4. The Normalization Process
- Normalizing a Database
- Summary
- Q&A
- Workshop
- Hour 5. Manipulating Data
- Overview of Data Manipulation
- Populating Tables with New Data
- Updating Existing Data
- Deleting Data from Tables
- Summary
- Q&A
- Workshop
- Hour 6. Managing Database Transactions
- What Is a Transaction?
- What Is Transactional Control?
- Transactional Control and Database Performance
- Summary
- Q&A
- Workshop
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- What Is a Query?
- Introduction to the <tt>SELECT</tt> Statement
- Examples of Simple Queries
- Summary
- Q&A
- Workshop
- Hour 8. Using Operators to Categorize Data
- What Is an Operator in SQL?
- Comparison Operators
- Logical Operators
- Conjunctive Operators
- Negating Conditions with the <tt>NOT</tt> Operator
- Arithmetic Operators
- Summary
- Q&A
- Workshop
- Hour 9. Summarizing Data Results from a Query
- What Are Aggregate Functions?
- Summary
- Q&A
- Workshop
- Hour 10. Sorting and Grouping Data
- Why Group Data?
- The <tt>GROUP BY</tt> Clause
- <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
- The <tt>HAVING</tt> Clause
- Summary
- Q&A
- Workshop
- Hour 11. Restructuring the Appearance of Data
- The Concepts of ANSI Character Functions
- Various Common Character Functions
- Miscellaneous Character Functions
- Mathematical Functions
- Conversion Functions
- The Concept of Combining Character Functions
- Summary
- Q&A
- Workshop
- Hour 12. Understanding Dates and Times
- How Is a Date Stored?
- Date Functions
- Date Conversions
- Summary
- Q&A
- Workshop
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Selecting Data from Multiple Tables
- Types of Joins
- Join Considerations
- Summary
- Q&A
- Workshop
- Hour 14. Using Subqueries to Define Unknown Data
- What Is a Subquery?
- Embedding a Subquery Within a Subquery
- Summary
- Q&A
- Workshop
- Hour 15. Combining Multiple Queries into One
- Single Queries Versus Compound Queries
- Why Would I Ever Want to Use a Compound Query?
- Compound Query Operators
- Using an <tt>ORDER BY</tt> with a Compound Query
- Using <tt>GROUP BY</tt> with a Compound Query
- Retrieving Accurate Data
- Summary
- Workshop
- Q&A
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- What Is an Index?
- How Do Indexes Work?
- The <tt>CREATE INDEX</tt> Command
- Types of Indexes
- When Should Indexes Be Considered?
- When Should Indexes Be Avoided?
- Summary
- Q&A
- Workshop
- Hour 17. Improving Database Performance
- What Is SQL Statement Tuning?
- Database Tuning Versus SQL Tuning
- Formatting Your SQL Statement
- Full Table Scans
- Other Performance Considerations
- Performance Tools
- Summary
- Q&A
- Workshop
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Users Are the Reason
- The Management Process
- Tools Utilized by Database Users
- Summary
- Q&A
- Workshop
- Hour 19. Managing Database Security
- What Is Database Security?
- How Does Security Differ from User Management?
- What Are Privileges?
- Controlling User Access
- Controlling Privileges Through Roles
- Summary
- Q&A
- Workshop
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- What Is a View?
- Creating Views
- Dropping a View
- What Is a Synonym?
- Summary
- Q&A
- Workshop
- Hour 21. Working with the System Catalog
- What Is the System Catalog?
- How Is the System Catalog Created?
- What Is Contained in the System Catalog?
- Examples of System Catalog Tables by Implementation
- Querying the System Catalog
- Updating System Catalog Objects
- Summary
- Q&A
- Workshop
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Advanced Topics
- Cursors
- Stored Procedures and Functions
- Triggers
- Dynamic SQL
- Call-Level Interface
- Using SQL to Generate SQL
- Direct Versus Embedded SQL
- Summary
- Q&A
- Workshop
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- SQL and the Enterprise
- Accessing a Remote Database
- Accessing a Remote Database Through a Web Interface
- SQL and the Internet
- SQL and the Intranet
- Summary
- Q&A
- Workshop
- Hour 24. Extensions to Standard SQL
- Various Implementations
- Examples of Extensions from Some Implementations
- Interactive SQL Statements
- Summary
- Q&A
- Workshop
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- SQL Statements
- SQL Clauses
- Appendix B. Using MySQL for Exercises
- Windows Installation Instructions
- Linux Installation Instructions
- Appendix C. Answers to Quizzes and Exercises
- Hour 1, "Welcome to the World of SQL"
- Hour 2, "Defining Data Structures"
- Hour 3, "Managing Database Objects"
- Hour 4, "The Normalization Process"
- Hour 5, "Manipulating Data"
- Hour 6, "Managing Database Transactions"
- Hour 7, "Introduction to the Database Query"
- Hour 8, "Using Operators to Categorize Data"
- Hour 9, "Summarizing Data Results from a Query"
- Hour 10, "Sorting and Grouping Data"
- Hour 11, "Restructuring the Appearance of Data"
- Hour 12, "Understanding Dates and Time"
- Hour 13, "Joining Tables in Queries"
- Hour 14, "Using Subqueries to Define Unknown Data"
- Hour 15, "Combining Multiple Queries into One"
- Hour 16, "Using Indexes to Improve Performance"
- Hour 17, "Improving Database Performance"
- Hour 18, "Managing Database Users"
- Hour 19, "Managing Database Security"
- Hour 20, "Creating and Using Views and Synonyms"
- Hour 21, "Working with the System Catalog"
- Hour 22, "Advanced SQL Topics"
- Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
- Hour 24, "Extensions to Standard SQL"
- Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
- <tt>EMPLOYEE_TBL</tt>
- <tt>EMPLOYEE_PAY_TBL</tt>
- <tt>CUSTOMER_TBL</tt>
- <tt>ORDERS_TBL</tt>
- <tt>PRODUCTS_TBL</tt>
- Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
- <tt>INSERT</tt> Statements
- Appendix F. Glossary
- Appendix G. Bonus Exercises
Recommended Book
Recommended Book
Recommended Book
Date Conversions
The conversion of dates can take place for any number of reasons. Conversions are mainly used to alter the data type of values defined as a DATETIME value or any other valid data type of a particular implementation.
Typical reasons for date conversions are as follows:
- To compare date values of different data types
- To format a date value as a character string
- To convert a character string into a date format
The ANSI CAST operator is used to convert data types into other data types.
The basic syntax is as follows:
CAST ( EXPRESSION AS NEW_DATA_TYPE )
Specific examples according to the syntax of some implementations are illustrated in the following subsections, covering
- The representation of parts of a DATETIME value
- Conversions of dates to character strings
- Conversions of character strings to dates
Date Pictures
|
A date picture is composed of formatting elements used to extract date and time information from the database in a desired format. Date pictures may not be available in all SQL implementations. |
Without the use of a date picture and some type of conversion function, the date and time information is retrieved from the database in a default format, such as
1999-12-31 31-DEC-99 1999-12-31 23:59:01.11 ...
What if you wanted the date displayed as the following? You would have to convert the date from a DATETIME format into a character string format:
December 31, 1997
This is accomplished by implementation-specific functions for this very purpose, further illustrated in the following sections.
|
Sybase date pictures: |
|
|
yy |
Year |
|
|
Quarter |
|
mm |
Month |
|
dy |
Day of year |
|
wk |
Week |
|
dw |
Weekday |
|
hh |
Hour |
|
mi |
Minute |
|
ss |
Second |
|
ms |
Millisecond |
|
Oracle date pictures: |
|
|
AD |
Anno Domini |
|
AM |
Ante meridian |
|
BC |
Before Christ |
|
CC |
Century |
|
D |
Number of the day in the week |
|
DD |
Number of the day in the month |
|
DDD |
Number of the day in the year |
|
DAY |
The day spelled out (MONDAY) |
|
Day |
The day spelled out (Monday) |
|
day |
The day spelled out (monday) |
|
DY |
The three-letter abbreviation of day (MON) |
|
Dy |
The three-letter abbreviation of day (Mon) |
|
dy |
The three-letter abbreviation of day (mon) |
|
HH |
Hour of the day |
|
HH12 |
Hour of the day |
|
HH24 |
Hour of the day for a 24-hour clock |
|
J |
Julian days since 12-31-4713 B.C. |
|
MI |
Minute of the hour |
|
MM |
The number of the month |
|
MON |
The three-letter abbreviation of the month (JAN) |
|
Mon |
The three-letter abbreviation of the month (Jan) |
|
mon |
The three-letter abbreviation of the month (jan) |
|
MONTH |
The month spelled out (JANUARY) |
|
Month |
The month spelled out (January) |
|
month |
The month spelled out (january) |
|
PM |
Post meridian |
|
Q |
The number of the quarter |
|
RM |
The Roman numeral for the month |
|
RR |
The two digits of the year |
|
SS |
The second of a minute |
|
SSSSS |
The seconds since midnight |
|
SYYYY |
The signed year; if B.C. 500, B.C. = -500 |
|
W |
The number of the week in a month |
|
WW |
The number of the week in a year |
|
Y |
The last digit of the year |
|
YY |
The last two digits of the year |
|
YYY |
The last three digits of the year |
|
YYYY |
The year |
|
YEAR |
The year spelled out (NINETEEN-NINETY-NINE) |
|
Year |
The year spelled out (Nineteen-Ninety-Nine) |
|
year |
The year spelled out (nineteen-ninety-nine) |
|
MySQL date pictures: |
|
|
SECOND |
Seconds |
|
MINUTE |
Minutes |
|
HOUR |
Hours |
|
DAY |
Days |
|
MONTH |
Months |
|
YEAR |
Years |
|
MINUTE_SECOND |
Minutes and seconds |
|
HOUR_MINUTE |
Hours and minutes |
|
DAY_HOUR |
Days and hours |
|
YEAR_MONTH |
Years and months |
|
HOUR_SECOND |
Hours, minutes, and seconds |
|
DAY_MINUTE |
Days and minutes |
|
DAY_SECOND |
Days and seconds |
Converting Dates to Character Strings
DATETIME values are converted to character strings to alter the appearance of output from a query. A conversion function is used to achieve this. Two examples, the first using SQL Server, of converting date and time data into a character string as designated by a query follow:
SELECT DATE_HIRE = DATENAME(MONTH, DATE_HIRE) FROM EMPLOYEE_PAY_TBL
DATE_HIRE --------- May June August June July Jan 6 rows affected.
The following is an Oracle date conversion using the TO_CHAR function:
Converting Character Strings to Dates
The following example illustrates a method from one implementation of converting a character string into a date format. When the conversion is complete, the data can be stored in a column defined as having some form of a DATETIME data type.
You may be wondering why six rows were selected from this query when only one date value was provided. The reason is because the conversion of the literal string was selected from the EMPLOYEE_PAY_TBL, which has six rows of data. Hence, the conversion of the literal string was selected against each record in the table.