Sql server® 2012 t-sql fundamentals

 

CHAPTER 2

 

Single-Table Queries

 

 81

As you probably noticed, none of the functions return only the current system date or only the 

current system time. However, you can get those easily by converting CURRENT_TIMESTAMP or 

SYSDATETIME to DATE or TIME like this.

SELECT 

  CAST(SYSDATETIME() AS DATE) AS [current_date], 

  CAST(SYSDATETIME() AS TIME) AS [current_time];

The 

CAST

, 

CONVERT

, and 

PARSE

 Functions and Their 

TRY

_ Counterparts

The CAST, CONVERT and PARSE functions are used to convert an input value to some target type. If 

the conversion succeeds, the functions return the converted value; otherwise, they cause the query to 

fail. The three functions have counterparts called TRY_CAST, TRY_CONVERT, and TRY_PARSE, respec-

tively. Each version with the prefix TRY_ accepts the same input as its counterpart, and does the same 

thing; the difference is that if the input isn’t convertible to the target type, the function returns a 

NULL instead of failing the query.

The functions TRY_CAST, TRY_CONVERT, PARSE, and TRY_PARSE were added in SQL Server 2012.

Syntax

CAST(value AS datatype)

TRY_CAST(value AS datatype)

CONVERT (datatype, value [, style_number])

TRY_CONVERT (datatype, value [, style_number])

PARSE (value AS datatype [USING culture])

TRY_PARSE (value AS datatype [USING culture])

All three base functions convert the input value to the specified target datatype. In some cases, 

CONVERT has a third argument with which you can specify the style of the conversion. For example, 

when you are converting from a character string to one of the date and time data types (or the other 

way around), the style number indicates the format of the string. For example, style 101 indicates 

‘MM/DD/YYYY’, and style 103 indicates ‘DD/MM/YYYY’. You can find the full list of style numbers and 

their meanings in SQL Server Books Online under “CAST and CONVERT.” Similarly, where applicable, 

the PARSE function supports indication of a culture—for example, ‘en-US’ for U.S. English and ‘en-GB’ 

for British English. 

As mentioned earlier, when you are converting from a character string to one of the date and time 

data types, some of the string formats are language dependent. I recommend either using one of the 

language-neutral formats or using the CONVERT/PARSE functions and explicitly specifying the style 

number or culture. This way, your code is interpreted the same way regardless of the language of the 

logon running it.

www.it-ebooks.info

---

82  

Microsoft SQL Server 2012 T-SQL Fundamentals

Note that CAST is ANSI and CONVERT and PARSE aren’t, so unless you need to use the style num-

ber or culture, it is recommended that you use the CAST function; this way, your code is as standard 

as possible.

Following are a few examples of using the CAST, CONVERT, and PARSE functions with date and time 

data types. The following code converts the character string literal ‘20090212’ to a DATE data type.

SELECT CAST('20090212' AS DATE);

The following code converts the current system date and time value to a DATE data type, practi-

cally extracting only the current system date.

SELECT CAST(SYSDATETIME() AS DATE);

The following code converts the current system date and time value to a TIME data type, practi-

cally extracting only the current system time.

SELECT CAST(SYSDATETIME() AS TIME);

As suggested earlier, if you need to work with the DATETIME or SMALLEDATETIME types (for exam-

ple, to be compatible with systems using versions earlier than SQL Server 2008) and want to represent 

only a date or only a time, you can “zero” the irrelevant part. In other words, to work only with dates, 

you set the time to midnight. To work only with time, you set the date to the base date January 1, 1900. 

The following code converts the current date and time value to CHAR(8) by using style 112 

(‘YYYYMMDD’).

SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112);

For example, if the current date is February 12, 2009, this code returns ‘20090212’. Remember that 

this style is language neutral, so when the code is converted back to DATETIME, you get the current 

date at midnight.

SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS DATETIME);

Similarly, to zero the date portion to the base date, you can first convert the current date and time 

value to CHAR(12) by using style 114 (‘hh:mm:ss.nnn’).

SELECT CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114);

When the code is converted back to DATETIME, you get the current time on the base date.

SELECT CAST(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) AS DATETIME);

As for using the PARSE function, here are a couple of examples that I also demonstrated previously 

in this chapter.

SELECT PARSE('02/12/2007' AS DATETIME USING 'en-US'); 

SELECT PARSE('02/12/2007' AS DATETIME USING 'en-GB');

www.it-ebooks.info

---