SQLServerCh08

Chapter 8 How to work with data types Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 1 Objectives Applied  Code queries that use the data conversion functions to work with the data types presented in this chapter. Knowledge  Describe the data that can be stored in any of the string, numeric, date/time, and large value data types.  Describe the difference between standard character data and Unicode character data.  Describe the differences between implicit and explicit data type conversion. Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 2 SQL Server data type categories  String  Numeric  Temporal (date/time)  Other Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 3 ANSI-standard data types and SQL Server equivalents Synonym for ANSI-standard data type binary varying char varying character dec double precision float integer Book Title, C1 SQL Server data type used varbinary varchar character varying char decimal float real or float int © 2012, Mike Murach & Associates, Inc. Slide 4 ANSI-standard data types and SQL Server equivalents (continued) Synonym for ANSI-standard data type national char national char varying national text rowversion Book Title, C1 SQL Server data type used nchar national character nvarchar national character varying ntext timestamp © 2012, Mike Murach & Associates, Inc. Slide 5 The integer data types Type bigint int smallint tinyint bit Book Title, C1 Bytes 8 4 2 1 1 © 2012, Mike Murach & Associates, Inc. Slide 6 The decimal data types Type decimal[(p[,s])] numeric[(p[,s])] money 8 smallmoney Book Title, C1 Bytes 5-17 5-17 4 © 2012, Mike Murach & Associates, Inc. Slide 7 The real data types Type float[(n)] real Book Title, C1 Bytes 4 or 8 4 © 2012, Mike Murach & Associates, Inc. Slide 8 Terms  Precision  Scale  Exact numeric data types  Floating-point number  Significant digits Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 9 String data types for storing standard characters Type char[(n)] varchar[(n)] Bytes n String data types for storing Unicode characters Type nchar(n) nvarchar(n) Book Title, C1 Bytes 2×n © 2012, Mike Murach & Associates, Inc. Slide 10 Terms  Unicode characters  Fixed-length strings  Variable-length strings Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 11 Date/time data types prior to SQL Server 2008 Type datetime smalldatetime Bytes 8 4 Date/time data types for SQL Server 2008 and later Type date 3 time(n) 3-5 datetime2(n) datetimeoffset(n) Book Title, C1 Bytes 6-8 8-10 © 2012, Mike Murach & Associates, Inc. Slide 12 Common date formats Format Example yyyy-mm-dd mm/dd/yyyy mm-dd-yy Month dd, yyyy Mon dd, yy dd Mon yy 2012-04-30 4/30/2012 4-30-12 April 30, 2012 Apr 30, 12 30 Apr 12 Common time formats Format Example hh:mi 16:20 hh:mi am/pm hh:mi:ss hh:mi:ss:mmm hh:mi:ss.nnnnnnn 4:20 pm 4:20:36 4:20:36:12 4:20:36.1234567 Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 13 How to use date/time literals  To code a date/time literal, enclose the date/time value in single quotes.  If you don’t specify a time in a date/time value, the time defaults to 12:00 a.m.  If you don’t specify a date in a date/time value, the date defaults to January 1, 1900.  By default, the years 00 to 49 are interpreted as 2000 to 2049 and the years 50 through 99 are interpreted as 1950 through 1999.  You can specify a time using either a 12-hour or a 24-hour clock.  For a 12-hour clock, am is the default. Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 14 The large value data types for SQL Server 2005 and later  varchar(max)  nvarchar(max)  varbinary(max) How the large value data types map to the old large object types SQL Server 2005 and later varchar(max) nvarchar(max) varbinary(max) Book Title, C1 Prior to 2005 text ntext image © 2012, Mike Murach & Associates, Inc. Slide 15 Order of precedence for common data types Precedence Category Data type Highest Date/time datetime smalldatetime float real decimal money smallmoney int smallint tinyint bit nvarchar nchar varchar char Numeric String Lowest Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 16 Conversions that can’t be done implicitly From data type char, varchar, nchar, nvarchar datetime, smalldatetime money, smallmoney To data type money, smallmoney decimal, numeric, float, real, bigint, int, smallint, tinyint, money, smallmoney, bit char, varchar, nchar, nvarchar Expressions that use implicit conversion InvoiceTotal * .0775 -- InvoiceTotal (money) converted to decimal PaymentTotal – 100 -- Numeric literal converted to money PaymentDate = '2012-04-05' -- Date literal converted to smalldatetime value Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 17 Terms  Implicit conversion  Explicit conversion Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 18 The syntax of the CAST function CAST(expression AS data_type) A SELECT statement that uses the CAST function SELECT InvoiceDate, InvoiceTotal, CAST(InvoiceDate AS varchar) AS varcharDate, CAST(InvoiceTotal AS int) AS integerTotal, CAST(InvoiceTotal AS varchar) AS varcharTotal FROM Invoices; Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 19 How to convert data when performing integer division Operation Result 50/100 50/CAST(100 AS decimal(3)) 0 .500000 Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 20 The syntax of the CONVERT function CONVERT(data_type, expression [, style]) Convert and format dates SELECT CONVERT(varchar, InvoiceDate) AS varcharDate, CONVERT(varchar, InvoiceDate, 1) AS varcharDate_1, CONVERT(varchar, InvoiceDate, 107) AS varcharDate_107, CONVERT(varchar, InvoiceTotal) AS varcharTotal, CONVERT(varchar, InvoiceTotal, 1) AS varcharTotal_1 FROM Invoices; Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 21 Style codes for converting date/time data to character data Code 0 or 100 (default) 1 or 101 7 or 107 8 or 108 10 or 110 12 or 112 14 or 114 Book Title, C1 Output format Mon dd yyyy hh:miAM/PM mm/dd/yy or mm/dd/yyyy Mon dd, yy or Mon dd, yyyy hh:mi:ss mm-dd-yy or mm-dd-yyyy yymmdd or yyyymmdd hh:mi:ss:mmm (24-hour clock) © 2012, Mike Murach & Associates, Inc. Slide 22 Style codes for converting real data to character data Code 0 (default) 1 2 Book Title, C1 Output 6 digits maximum 8 digits; must use scientific notation 16 digits; must use scientific notation © 2012, Mike Murach & Associates, Inc. Slide 23 Style codes for converting money data to character data Code 0 (default) 1 2 Book Title, C1 Output 2 digits to the right of the decimal point; no commas to the left 2 digits to the right of the decimal point; commas to the left 4 digits to the right of the decimal point; no commas to the left © 2012, Mike Murach & Associates, Inc. Slide 24 The syntax of the TRY_CONVERT function TRY_CONVERT(data_type, expression [, style ]) Convert and format dates SELECT TRY_CONVERT(varchar, InvoiceDate) AS varcharDate, TRY_CONVERT(varchar, InvoiceDate, 1) AS varcharDate_1, TRY_CONVERT(varchar, InvoiceDate, 107) AS varcharDate_107, TRY_CONVERT(varchar, InvoiceTotal) AS varcharTotal, TRY_CONVERT(varchar, InvoiceTotal, 1) AS varcharTotal_1, TRY_CONVERT(date, 'Feb 29 2011') AS invalidDate FROM Invoices; Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 25 Other data conversion functions  STR(float[,length[,decimal]])  CHAR(integer)  ASCII(string)  NCHAR(integer)  UNICODE(string) Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 26 Examples that use the data conversion functions Function Result STR(1234.5678, 7, 1) CHAR(79) ASCII('Orange') NCHAR(332) UNICODE(N'Or') 1234.6 O 79 O 332 Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 27 ASCII codes for common control characters Control character Tab Line feed Carriage return Value Char(9) Char(10) Char(13) Use the CHAR function to format output SELECT VendorName + CHAR(13) + CHAR(10) + VendorAddress1 + CHAR(13) + CHAR(10) + VendorCity + ', ' + VendorState + ' ' + VendorZipCode FROM Vendors WHERE VendorID = 1; US Postal Service Attn: Supt. Window Services Madison, WI 53707 Book Title, C1 © 2012, Mike Murach & Associates, Inc. Slide 28