SQL CAST() | CONVERT() Function

Main Article :- Sql difference between CAST() and CONVERT() Functions

Whatever you can do with CAST, you can do it with CONVERT. Since CAST and CONVERT are interchangeable in most situations, you may be wondering if one function is better than the other.

CAST and CONVERT are similar in many ways. Both functions can convert a value from one formation to another. The difference between the two is that CAST does not accept date conversion parameters. This can be used by COVERT to format the date as a string.

• CAST is part of the SQL-92 specification, but CONVERT is not.
• Use CAST if you want to allow SQL to be ported between databases.
• The Cast () function is used to convert a data type variable or data from one data type to another. CAST is ANSI-SQL compliant.
• Therefore, it is suitable for use in other database implementations. There is no performance degradation when using CAST. The Cast () function provides a data type for dynamic parameters (?) Or null values.

In many cases, to convert an expression from one type to another, you need to convert the data from a datetime type to a varchar type in a stored procedure or other routine. For such things, conversion functions are used. You can use the CONVERT () function to display date / time data in a variety of formats.

CONVERT differs in that it accepts optional style parameters used for formatting.

Syntax:

CAST ( [Expression]
AS Datatype)

Target type refers to the data type that you are casting an expression to. The source type is determined by the data type of the expression you are casting from.

Syntax:

CONVERT(data_type(length), expression, style)

Style - style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Example 1: Here is an example using both functions in the same statement:

SELECT CAST ('10' as int) * 20,
CONVERT (int, '10') * 20

In both instances, we are translating from the text value "10" to its integer equivalent.

This example should help you remember how to use the CAST and CONVERT functions. If not, read Data Type Conversion Functions on my site to learn everything.

Example 2: You can define the format of the resulting date, such as YYYY/MM/DD or MM/DD/YYYY, when converting a DateTime datatype to Varchar.

SELECT CONVERT(VARCHAR,GETDATE(),101) as MMDDYYYY,
CONVERT(VARCHAR,GETDATE(),111) as YYYYMMDD

The numbers shown in red are the style formatter. There are many style formats you can use. The complete list is here.

Example 3:

DECLARE @A varchar(2)  
DECLARE @B varchar(2)  
DECLARE @C varchar(2)  
set @A=25  
set @B=15  
set @C=33  
Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result 

Example 4:

DECLARE @Z char(30)  
SELECT @Z=current_timestamp  
select CAST (@Z as date) as result 

Example 5: In this example we take a style value 108 which defines the following format:

hh:mm:ss

Now use the above style in the following query:

select convert(varchar(20),GETDATE(),108) 

Example 6: In this example we use the style value 107 which defines the following format:

Mon dd, yy

Now use that style in the following query:

select convert(varchar(20),GETDATE(),107)

Example 7: In this example we see different style values which defines the following format.

SELECT CONVERT(VARCHAR(15),GETDATE(),6)  
go  
SELECT CONVERT(VARCHAR(16),GETDATE(),106)  
go  
SELECT CONVERT(VARCHAR(24),GETDATE(),113)

select convert(varchar(20),GETDATE(),108)  

Example 8: In this example we use the style value 107 which defines the following format:

Mon dd, yy

Now use that style in the following query:

select convert(varchar(20),GETDATE(),107)

Example 9: In this example we see different style values which defines the following format.

SELECT CONVERT(VARCHAR(15),GETDATE(),6)  
go  
SELECT CONVERT(VARCHAR(16),GETDATE(),106)  
go  
SELECT CONVERT(VARCHAR(24),GETDATE(),113)  

Example 10: CAST / CONVERT Example

Expression here refers to any value or expression you wish to convert, and data type designates the new data type.

Each instance converts the ListPrice to an int and then fetches the product name for all products that have a 3 in the first digit of their list price.

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

The time component is lost when converting a datetime value to a date, as is clear. However, one advantage is that the storage size is decreased from 8 bytes to 3 bytes. In any case, you would only do this conversion if you were omitting the time component.

The date, time, and a three-digit fractional second component are all included in the datetime data type. It can be accurate to.000,.003, or.007 seconds in steps.

We also have the datetime value: 2018-09-01 11:50:05.627.

The date data type, however, only has a one-day accuracy (and, as previously indicated, does not include time).

Syntax:

The following statement converts a datetime value to a date using the CAST() function:

CAST(datetime_expression AS DATE)

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between datetime and date.

DECLARE 
  @thedatetime datetime, 
  @thedate date;
SET @thedatetime = '2025-05-21 10:15:30.123';
SET @thedate = @thedatetime;
SELECT 
  @thedatetime AS 'datetime',
  @thedate AS 'date';

Result:

This is an implicit conversion because it does not use a conversion function (such as the one below) to convert explicitly. In this case, when you try to assign a datetime value to a date variable, SQL Server performs an implicit conversion in the background.

In this example, you can see that the date value contains only the date (not the time component).

Example 2: Explicit Conversion using CAST():

This is an example of an explicit conversion. In this case, the CAST () function is used in the SELECT statement to explicitly convert the date and time.

DECLARE @thedatetime datetime;
SET @thedatetime = '2025-05-21 10:15:30.123';
SELECT 
  @thedatetime AS 'datetime',
  CAST(@thedatetime AS date) AS 'date';

Result:

Example 3: This example uses the CAST() function to convert the current datetime to a date value:

SELECT 
    CAST(GETDATE() AS DATE) date;

Output:

A data type that only allows true or false decisions and is utilised in CREATE TABLE and ALTER TABLE instructions.

Syntax:

In the column definition of a CREATE TABLE statement:

column_name BOOLEAN

• Range: TRUE or FALSE. Do not enclose the literal values ​​TRUE and FALSE in quotation marks. Literal values ​​can be written in uppercase, lowercase, or uppercase. Values ​​retrieved from the table are always returned as lowercase true or false.
• Conversion: Impala does not automatically convert other types to BOOLEAN. All conversions must use an explicit call to the CAST () function.

Example 1: You can use CAST () to convert any integer or floating point type to BOOLEAN. The value 0 represents false and non-zero values ​​are converted to true.

SELECT CAST(42 AS BOOLEAN) AS nonzero_int, 
CAST(99.44 AS BOOLEAN) AS nonzero_decimal,
CAST(000 AS BOOLEAN) AS zero_int, CAST(0.0 AS BOOLEAN) AS zero_decimal;

Output:

Example 2: When you cast the opposite way, from BOOLEAN to a numeric type, the result becomes either 1 or 0:

SELECT CAST(true AS INT) AS true_int, CAST(true AS DOUBLE) AS true_double,
CAST(false AS INT) AS false_int, CAST(false AS DOUBLE) AS false_double;

Output:

DECIMAL values can be converted to BOOLEAN, and zero and nonzero values will be handled similarly to other numeric types. A BOOLEAN cannot be cast to a DECIMAL.

A STRING value cannot be cast into a BOOLEAN value, however a BOOLEAN value can be cast into a STRING and return '1' for true values and '0' for false values.

Despite the fact that you can cast a TIMESTAMP to a BOOLEAN or a BOOLEAN to a BOOLEAN, the results are probably not going to be very helpful. When converted to BOOLEAN, any non-zero TIMESTAMP (i.e., any value other than 1970-01-01 00:00:00) becomes TRUE, whereas 1970-01-01 00:00:00 becomes FALSE. When expressed in BOOLEAN, the value of FALSE is 1970-01-01 00:00:00, while the value of TRUE is 1970-01-01 00:00:00:01, one second after this epoch date.

Example 3: Let us say you have the following int column amount in your table.

mysql> select amount from product_orders;

Output:

Example 4: Let's imagine you want to convert an int to a boolean where 200 is True if nothing else. Here is the SQL statement to convert an int to a boolean.

mysql> select cast(amount<200 as unsigned) from product_orders;

Output:

Example 5: Each of the aforementioned queries can alternatively be performed using MySQL Convert rather than MySQL CAST. Using MySQL CONVERT, below is an example of the above query.

mysql> select convert(amount<200, unsigned) from product_orders;

Example 6: PostgreSQL cast int to boolean:

In Postgresql, to cast or convert from int to boolean use the below command.

SELECT CAST( 1 AS boolean ); --CAST TO TRUE BOOLEAN VALUE

OR

SELECT CAST( 0 AS boolean ); --CAST TO FALSE BOOLEAN VALUE

In the above output, it converts 1 ( integer ) to a true ( boolean ) value and when we change the value to 0 ( integer ), it will convert that value to a false ( boolean ) value.

An integer can be changed into a DECIMAL data type using the CAST () function. Following an AS keyword and a new data type, this function also accepts an expression or column name as an argument.

Example 1: The columns in the SQL Server table have an int height. You must divide and output the result as a decimal in the query.

Select (height/10) as HeightDecimal

How would I cast and so that the HeightDecimal is no longer integer?

SELECT height/10.0 AS HeightDecimal FROM dbo.whatever;

If you require a specific precision scale, then say so:

SELECT CONVERT(DECIMAL(16,4), height/10.0) AS HeightDecimal
FROM dbo.whatever;

Example 2: You’d like to convert an integer value to a DECIMAL data type in SQL Server.

Let’s convert an integer to the DECIMAL data type.

We’ll use the CAST() function. Here’s the query you’d write:

SELECT
  CAST(12 AS DECIMAL(7,2) ) AS decimal_value;

Output:

Discussion: In this example, we converted an integer (12) to a decimal value (12.00). DECIMAL in CAST () has two decimal places, so the value displayed is two decimal places.

In SQL Server, you'll be able to utilize the CAST() work to change over an expression of one information sort to another. This work works nearly the same as the CONVERT() work, but the sentence structure is marginally distinctive (and CAST() doesn’t accept the fashion argument).

So in the event that you would like to change over a string to a date/time esteem, you'll utilize the Change over() function or the CAST() function.

And if you get a mistake while utilizing those two capacities, the PARSE() work can be fair to what you would like.

Syntax:

CAST ( expression AS data_type [ ( length ) ] )

These arguments are defined as follows:

Expression

Any valid expression.

• data_type: The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
length: An optional integer that specifies the length of the target data type. The default value is 30.

Example 1: Convert to date Data Type

In this example, we convert the string into a date data type (we specify this by using date as the second argument).

SELECT CAST('20 Dec 2018' AS date) AS Result;

Result:

A bit like with Convert() the string has to be in a fashion that’s able to be changed over to the desired information sort, something else you’ll get an error:

SELECT CAST('Homer' AS date) AS Result;

Conversion failed when converting date and/or time from character string.

Example 2: Using SQL Server, convert string to date explicitly:

SELECT CAST('12/01/2019' as date) as StringToDate , 
CAST(GETDATE() as VARCHAR(50)) as DateToString

Note that in SQL Server, changing over a string to date utilizing CAST() work depends on the dialect settings comparative to certain transformation, as we said within the previous section, so you'll be able as it were change over ISO groups or bolstered groups by the current dialect settings.

Example 3: Now to convert a string data type to date, we can execute the following query.

SELECT CAST('16 June 2021' AS date) AS Result;

Within the over inquiry, we are utilizing the Cast() work inside the SELECT articulation. And within the Cast() work, we have given the input expression as ’16 June 2021', and we moreover need the information sort to be a date.

Example 1: Any numeric value cannot be easily converted to the yyyymm date format in SQL Server. For this implementation, the integer must be formatted correctly (yyyymmdd), and we must use the Convert() and Left() functions in conjunction to change it to the yyyymm format.

DECLARE @Number INT  
SET @Number=20210622  
SELECT @Number AS 'Integer', 
 LEFT(CONVERT(varchar, @Number,112),6) AS 'YYYYMM'

First, we have defined an integer variable with a value in the aforementioned query. The number is then converted to yyyymmdd format using the Convert() method. The LEFT() function is then used to choose 6 characters from the string's beginning by using the Convert() function's output.

Example 2: Convert int to date in SQL Server yyyymmdd

Now we can use the Convert() function in SQL Server to convert an integer data type to the yyyymmdd date format. We can supply a style code in the Convert() function to format the result in yyyymmdd format. Take the next illustration as evidence for this.

DECLARE @Number INT  
SET @Number=20210622  
SELECT @Number AS 'Integer', 
CONVERT(varchar, @Number,112) AS 'YYYYMMDD'

The Convert() function is used in the example above to convert an integer variable to a varchar expression. Additionally, we declared the style code in the Convert() function as 112. It will produce the following outcome after completion.

SQL Server has three different ways to convert an expression from an integer data type to varchar. Often you want to convert a value of one data type to another.

Syntax:

Int to string / varchar conversion using Cast ()

SQL Server Cast () is a function that converts a value of one data type to another. It has the following syntax.

CAST(expression AS datatype(length))

The Cast () function only takes two arguments. One represents the user's desired data type in expression, while the other represents the desired data type after the modification.

Example 1:

DECLARE @Number INT  
SET @Number=22062021  
SELECT CAST(@Number AS varchar(10)) AS Num1;

Therefore, in the above example, we defined a variable of data type Integer and assigned a value to the variable. Then use the Cast () function to convert the variable to a varchar data type of length 10. The query then returns the following result:

Example 2: See the following example:

SELECT
  '100'::INTEGER,
  '01-OCT-2015'::DATE;

Notice that the cast syntax with the cast operator (::) is PostgreSQL-specific and does not conform to the SQL standard.

Example 3: Cast a string to an integer example:

Let’s take some examples of using the CAST operator to convert a value of one type to another.

The following statement converts a string constant to an integer:

SELECT CAST ('100' AS INTEGER);

Example 4: CAST - convert a string to an integer:

If the expression cannot be converted to the target type, PostgreSQL will raise an error. See the following example:

SELECT CAST ('10C' AS INTEGER);

Output:

Example: Recently, the client displayed the following code, which automatically converts to a date 1900-01-01 when trying to convert an empty string to a date and time.

SELECT CAST('' AS DATE)

Running the above script will return the date and time of 1900. The existing code actually used a case expression to convert date 1900 to a null value. This was very tiring and had a negative impact on performance.

Here is a much simpler way to do the same:

DECLARE @Var VARCHAR(4) = ''
SELECT
CAST(NULLIF(@Var,'') as DATE)

Now instead of using the CASE statement we can just the above query and get our result as NULL.

The SQL CAST function converts the data type of an expression to the specified data type. CAST uses expr's data type if the data type of expr is a standard data type or a subclass of a standard data type such as% Library.String,% Library.Time,% Library.Date,% Library.TimeStamp. Can be converted.

A function that converts the specified expression to the specified data type.

CHAR or CHARACTER: Represents a number or string with a start character. VARCHAR without n defaults to a length of 30 characters when specified for CAST or CONVERT. Otherwise, the data type VARCHAR (no size specified) maps to the single character MAXLEN, as shown in the data type table. NCHAR corresponds to CHAR. NVARCHAR is equivalent to VARCHAR.

Syntax:

CAST(expr AS CHAR | CHARACTER | VARCHAR | NCHAR | NVARCHAR)

CHAR(n), CHARACTER(n), or VARCHAR(n): represent a numeric or a string by the number of characters specified by n.

Syntax:

CAST(expr AS CHAR(n) | CHARACTER(n) | VARCHAR(n) )

CHAR VARYING or CHARACTER VARYING: represent a numeric or a string by the number of characters in the original value.

Syntax:

CAST(expr AS CHAR VARYING | CHARACTER VARYING)

INT, INTEGER, BIGINT, SMALLINT, and TINYINT: represent a numeric by its integer portion. Decimal digits are truncated.

Syntax:

CAST(expr AS INT | INTEGER | BIGINT | SMALLINT | TINYINT)

DEC, DECIMAL, and NUMERIC: Represents a number divided by the number of digits in the original value. Converted using the InterSystems IRIS $ DECIMAL function. This function converts a $ DOUBLE value to a $ DECIMAL value. p (precision), if specified, is retained as part of the defined data type, but does not affect the value returned by CAST. If you specify a positive integer for the s (scale) value, the decimal value is rounded to the specified number of digits. (Display mode contains the appropriate number of trailing zeros, but in logical and ODBC modes they are truncated.) If you specify s = 0, the number is rounded to an integer. If you specify s = -1, the number is truncated to an integer.

Syntax:

CAST(expr AS DEC | DECIMAL | NUMERIC)

CAST(expr AS DEC(p[,s]) | DECIMAL(p[,s]) | NUMERIC(p[,s]) )

DOUBLE represents the IEEE floating point standard. For further details, refer to the ObjectScript $DOUBLE function.

Syntax:

CAST(expr AS DOUBLE)

MONEY and SMALLMONEY are currency numeric data types. The scale for currency data types is always 4.

Syntax:

CAST(expr AS MONEY | SMALLMONEY)

DATE: Represents a date. Dates can be represented in one of the following formats, depending on the context: Locale display date format (eg MM / DD / YYYY). ODBC date format (YYYY-MM-DD); or integer date storage format $ HOROLOG (nnnnn). $ HOROLOG datepart must be specified as an integer, not a numeric string.

Syntax:

CAST(expr AS DATE)

TIME: Represents time. Time can be expressed in one of the following formats, depending on the context: Locale display time format (eg hh: mm: ss). ODBC date format (hh: mm: ss); or integer time storage format $ HOROLOG (nnnnn). The $ HOROLOG time division value must be specified as an integer, not a numeric string.

Syntax:

CAST(expr AS TIME)

POSIXTIME: Represents a date and time stamp as an encoded 64-bit signed integer. Refer to Date, Time, PosixTime, and TimeStamp Data Types in Data Types for details.

Syntax:

CAST(expr AS POSIXTIME)

TIMESTAMP, DATETIME, and SMALLDATETIME: represents a date and time stamp with the format YYYY-MM-DD hh:mm:ss.nnn. This corresponds to the ObjectScript $ZTIMESTAMP special variable.

Syntax:

CAST(expr AS TIMESTAMP | DATETIME | SMALLDATETIME)

BIT represents a single binary value.

Syntax:

CAST(expr AS BIT)

BINARY, BINARY VARYING, and VARBINARY represent the value of the data type% Library.Binary (xDBC data type BINARY). The option length n defaults to 1 for BINARY and 30 for BINARY VARYING and VARBINARY. When converting to binary, no conversion is actually performed on the data. InterSystems IRIS truncates the length of the value to the specified length n.

Syntax:

CAST(expr AS BINARY | BINARY VARYING | VARBINARY)
CAST(expr AS BINARY(n) | BINARY VARYING(n) | VARBINARY(n) )

GUID represents a 36-character value of data type %Library.UniqueIdentifier. If you supply an expr longer than 36 characters, CAST returns the first 36 characters of expr. To generate a GUID value, use the %SYSTEM.Util.CreateGUID() method.

Syntax:

CAST(expr AS GUID)

See Data Types for a list of data types that InterSystems SQL supports. For conversions of additional data types, see the CONVERT function. InterSystems IRIS returns SQLCODE-376 if you define a CAST for a data type that is not supported.

Process 1: Casting the first string You may use a single character, the first n characters, or the complete string after converting the string to another character data type. Prior to starting the InterSystems SQL resolves string concatenation ('can' || 'not' = cannot) and embedded double quotes ('can"t'= can't). To do. There is no loss of leading or trailing spaces. Every time When a text is converted to a numeric type, zero is returned as the only digit.

Process 2: Casting to DATE, TIME, and TIMESTAMP

You can cast a character string to the DATE, TIME, or TIMESTAMP data type. The following operations result in a valid value:

DATE: A string with the 'yyyy-mm-dd' format can be converted to a date. The ODBC date format is the same as this string format. Checks are made for values and ranges. The year, month, and day must all fall within the range of 0001 to 9999 (inclusive); for instance, 02/29 is only valid on leap years). The month must also fall between the range of 01 through 12. When a date is incorrect, as 2013-02-29, it gives 1840-12-31. (logical date 0).

In the fields for the month and day, missing leading zeros are added. The display mode and the locale's date display format determine how this cast is shown. For instance, "2004-11-23" could appear as "11/23/2004." This cast is returned as the corresponding $HOROLOG date integer in embedded SQL. When cast to DATE in logical mode, an invalid ODBC date or a non-numeric string is shown as 0; date 0 is displayed as 1840-12-31.

TIME: A string of the following formats can be converted to TIME: hh:mm, hh:mm:ss, or hh:mm:ss.nn (with any number of n fractional second digits). The ODBC time format is the same as this string format. Checks are made for values and ranges. Addition of missing leading zeros. This cast is returned as the corresponding $HOROLOG time integer in embedded SQL. When cast to TIME in logical format, an incorrect ODBC time or a non-numeric string is interpreted as 0; time 0 is shown as 00:00:00.

TIMESTAMP: A string that contains a valid date, a valid time, or just a valid date can be converted to TIMESTAMP. Different formats are possible for the date component. The date part defaults to January 1, 1841. A time component that is omitted defaults to 00:00:00. Month and day missing leading zeros are added. A colon (:) or a period (.) can come before fractional seconds (if they are specified) (:). The significance of the symbols vary. A period denotes a common fraction, as in 12:00:00. 4 denotes a fraction of a second, while 12:00:00.004 denotes a fourth of a second. A colon denotes that the information that follows is in thousandths of a second; for example, 12:00:00:4 denotes a fourth of a second. A colon can only be followed by a maximum of three digits.

Process 3: Casting NULL and the Empty String:

The empty string ('') casts as follows:

All character data types return NULL.

With the necessary amount of following fractional zeros, all numeric data types yield 0 (zero). With no following fractional zeros, the DOUBLE data type returns a value of zero.

• The DATE data type returns 12/31/1840.
• The TIME data type returns 00:00:00.
• The TIMESTAMP, DATETIME, and SMALLDATETIME data types return NULL.
• The BIT data type returns 0.
• All binary data types return NULL.

Process 4: Casting Dates

Casting a date into a character, numeric, or date data type is possible.

A timestamp is a 64-bit signed encoded integer that is produced when a date is cast to the POSIXTIME data type. Since a date does not have a time component, the timestamp encoding is given the value 00:00:00. When CAST does date validation, it generates a SQLCODE -400 error if the expr value is not a valid date.

Casting a date to the TIMESTAMP, DATETIME, or SMALLDATETIME data type returns a timestamp with the format YYYY-MM-DD hh:mm:ss. Since a date does not have a time portion, the time portion of the resulting timestamp is always 00:00:00. CAST performs date validation; if the expr value is not a valid date, it issues an SQLCODE -400 error.

Example 1: The following Dynamic SQL example casts a field of DATE data type to TIMESTAMP and POSIXTIME:

SET myquery=2
SET myquery(1)="SELECT TOP 5 DOB,CAST(DOB AS TIMESTAMP) AS TStamp,"
SET myquery(2)="CAST(DOB AS POSIXTIME) AS Posix FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"

Example 2: The following Dynamic SQL example casts a field of TIMESTAMP data type to DATE and POSIXTIME:

SET myquery=2
SET myquery(1)="SELECT TOP 5 EventDate,CAST(EventDate AS DATE) AS Horolog,"
SET myquery(2)="CAST(EventDate AS POSIXTIME) AS Posix FROM Aviation.Event"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"

Casting a date to a numeric data type returns the $HOROLOG value for the date. This is an integer value representing the number of days since Dec. 31, 1840.

When a date is cast to a character data type, it either gives the entire date or as much of it as the data type's length will allow. However, not all character data types have the same display format. The whole date in display format is returned by the CHAR VARYING and CHARACTER VARYING data types. These data types, for instance, return the date as a character string with the same format whether the date is displayed as MM/DD/YYYY. The date (or a portion of it) is returned as a character string in ODBC date format by the other character data types. These data types return the date as a character string with the format YYYY-MM-DD, for instance, if a date appears as mm/dd/yyyy. Thus for the date 04/24/2004, the CHAR data type returns '2' (the first character of the year), and a CHAR(8) returns '2004–04–'.

Process 5: Casting a Bit Value:

To return a 0 or 1, you can cast an expr value as BIT. expr returns 1 if it is 1 or any other non-zero numeric value. expr returns 1 if it is "TRUE," "True," or "true." (The letter "T" cannot be used to shorten the word "True," which can be written in any combination of upper- and lowercase letters.) expr returns 0 if it is any other non-numeric value. It gives 0 if expr is 0.

Example 1: The first five CAST operations in the instance below return 1, whereas the subsequent second five CAST operations yield 0:

SELECT CAST(1 AS BIT) AS One, 
   CAST(7 AS BIT) AS Num,      
   CAST(743.6 AS BIT) AS Frac,  
   CAST(0.3 AS BIT) AS Zerofrac,
   CAST('tRuE' AS BIT) AS TrueWord,
   CAST(0 AS BIT) AS Zero,  
   CAST('FALSE' AS BIT) AS FalseWord, 
   CAST('T' AS BIT) AS T,    
   CAST('F' AS BIT) AS F,   
   CAST(0.0 AS BIT) AS Zerodot

Example 2: The CAST function is used in the example that follows to display an average as an integer rather than a floating point number. In contrast to rounding, the CAST truncates the number:

Example 3: The following example shows how the CAST function converts pi (a floating point number) to different numeric data types:

SELECT CAST({fn PI()} As INTEGER) As IntegerPi,
   CAST({fn PI()} As SMALLINT) As SmallIntPi,
   CAST({fn PI()} As DECIMAL) As DecimalPi,
   CAST({fn PI()} As NUMERIC) As NumericPi,
   CAST({fn PI()} As DOUBLE) As DoublePi

Example 4: The accuracy and scale values in the instance below are parsed, but they have no effect on the CAST-returned value:

SELECT CAST({fn PI()} As DECIMAL) As DecimalPi,
CAST({fn PI()} As DECIMAL(6,3)) As DecimalPSPi;

Example 5: The following example shows how the CAST function converts pi (a floating point number) to different character data types:

SELECT CAST({fn PI()} As CHAR) As CharPi,
   CAST({fn PI()} As CHAR(4)) As CharNPi,
   CAST({fn PI()} As CHAR VARYING) As CharVaryingPi,
   CAST({fn PI()} As VARCHAR(4)) As VarCharNPi;

Example 6: The following example shows how the CAST function converts Name (a character string) to different character data types:

SELECT DISTINCT 
CAST(Name As CHAR) As CharName,
CAST(Name As CHAR(4)) As CharNName,
CAST(Name As CHAR VARYING) As CharVaryingName,
CAST(Name As VARCHAR(4)) As VarCharNName
FROM Sample.Person;

Example 7: The results of converting Name (a character string) into various numeric data types are demonstrated in the example that follows. The value returned in each scenario is 0 (zero):

SELECT DISTINCT 
   CAST(Name As INT) As IntName,
   CAST(Name As SMALLINT) As SmallIntName,
   CAST(Name As DEC) As DecName,
   CAST(Name As NUMERIC) As NumericName
   FROM Sample.Person;

Example 8: In the sample that follows, a date field (DOB) is converted to several character data types as well as numeric data types. The $HOROLOG integer equivalent of a date is obtained by casting it to a number. When a date is converted to a character data type, one of two results is returned: either the date (partial or whole) in ODBC date string format, or a date string in input format (CHAR VARYING or CHARACTER VARYING):

SELECT DISTINCT DOB,
   CAST(DOB As INT) AS IntDate,
   CAST(DOB As CHAR) AS CharDate,
   CAST(DOB As CHAR(6)) AS CharNDate,
   CAST(DOB As CHAR VARYING) AS CharVaryDate,
   CAST(DOB As VARCHAR(10)) AS VarCharNDate
   FROM Sample.Person;

Example 9: The following example casts character strings to the DATE and TIME data types:

SELECT CAST('1936-11-26' As DATE) AS StringToDate,
CAST('14:33:45.78' AS TIME) AS StringToTime;

Only a string in the YYYY-MM-DD format can be transformed into a date. Other formatted strings return 0. When converting a string to the TIME data type, take note that fractional seconds are truncated rather than rounded.

Example 10: The following example casts a date to the TIMESTAMP data type:

SELECT DISTINCT DOB,
  CAST(DOB As TIMESTAMP) AS DateToTstamp
  FROM Sample.Person

The resulting timestamp is in the format: YYYY-MM-DD hh:mm:ss.

Example 11: The following example casts a character strig to the TIME data type, then casts the resulting time to the TIMESTAMP data type:

SELECT CAST(CAST('14:33:45.78' AS TIME) As TIMESTAMP) AS TimeToTstamp

The timestamp that results has the following format: YYYY-MM-DD hh:mm:ss. The nested CAST provides the time component, and the system date serves as the date component.