I am going pick up writing my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions. <\/p>\n
I will continue examining the Comparison Operators<\/a> today. <\/p>\n These comparison operators are known as the less than <<\/span>, less than or equal to <=<\/span>, equal to =<\/span>, greater than ><\/span>, and greater than or equal to >=<\/span> symbols in inequality<\/a> and equality<\/a> mathematics. <\/p>\n I will be examining how a sample operator works with character, date\/time, money and binary data types. Remember, character data types use the ASCII or UNICODE chart to determine if one character is greater than another. All other data types are based upon the value stored on disk or in memory.<\/p>\n The first example below shows the less than operator used with character data type.<\/p>\n The output of the first example is listed below.<\/p>\n The second example below shows the equal operator used with date\/time data type. In this example, both variables are equal due to precision loss during rounding.<\/p>\n The output of the second example is listed below.<\/p>\n The third example below shows the greater than operator used with money data type.<\/p>\n The output of the third example is listed below.<\/p>\n The fourth example below shows the not equal to operator used with binary data type.<\/p>\n The output of the fourth example is listed below.<\/p>\n\r\n\r\n--\r\n-- Comparision operators - Character data type\r\n--\r\n\r\n-- Declare variables\r\ndeclare @chr_one varchar(2) = 'AB';\r\ndeclare @chr_two varchar(2) = 'ab';\r\n\r\n-- Both variables are the same\r\nprint @chr_one;\r\nprint @chr_two;\r\nprint ' ';\r\n\r\n-- Ansi operator - <\r\nif @chr_one < @chr_two \r\n print 'CHAR - Upper is less than lower.';\r\nelse\r\n print 'CHAR - Lower is less than upper.';\r\nprint ' ';\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n\r\nAB\r\nab\r\n \r\nCHAR - Lower is less than upper.\r\n<\/pre>\n<\/p>\n
\r\n\r\n--\r\n-- Comparision operators - Date\/Time data type\r\n--\r\n\r\n-- Declare variables\r\ndeclare @dt_one smalldatetime = '2013-05-01 23:59:59.999';\r\ndeclare @dt_two smalldatetime = '2013-05-02 00:00:00.000'\r\n\r\n-- Both variables are the same (rounding)\r\nprint @dt_one;\r\nprint @dt_two;\r\nprint ' ';\r\n\r\n-- Ansi operator - =\r\nif @dt_two = @dt_one\r\n print 'DATE \/ TIME - Both variables are equal.';\r\nelse\r\n print 'DATE \/ TIME - Both variables are not equal.';\r\nprint ' ';\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n\r\nMay 2 2013 12:00AM\r\nMay 2 2013 12:00AM\r\n \r\nDATE \/ TIME - Both variables are equal.\r\n<\/pre>\n<\/p>\n
\r\n--\r\n-- Comparision operators - Money data type\r\n--\r\n\r\n-- Declare variables\r\ndeclare @num_one smallmoney = 321.22;\r\ndeclare @num_two smallmoney = 312.21;\r\n\r\n-- Both variables are not the same\r\nprint @num_one;\r\nprint @num_two;\r\nprint ' ';\r\n\r\n-- Ansi operator - >\r\nif @num_one > @num_two\r\n print 'MONEY - Variable one is greater than two.';\r\nelse\r\n print 'MONEY - Variable one is not greater than two.';\r\nprint ' ';\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n321.22\r\n312.21\r\n \r\nMONEY - Variable one is greater than two.\r\n<\/pre>\n<\/p>\n
\r\n\r\n--\r\n-- Comparision operators - Binary data type\r\n--\r\n\r\n-- Declare variables\r\ndeclare @bin_one binary(1) = 0x3E;\r\ndeclare @bin_two binary(1) = 0xE3;\r\n\r\n-- Both variables are not the same\r\nprint @bin_one;\r\nprint @bin_two;\r\nprint ' ';\r\n\r\n-- Ansi operator - <>\r\nif @bin_one <> @bin_two\r\n print 'BINARY - Variable one is not equal to two.';\r\nelse\r\n print 'BINARY - Variable one is equal to two.';\r\nprint ' ';\r\n<\/pre>\n<\/p>\n
\r\noutput: \r\n0x3E\r\n0xE3\r\n \r\nBINARY - Variable one is not equal to two.\r\n<\/pre>\n<\/p>\n