How to swap the values of two columns of a table ?
Let me explain the scenario with an example –
Let’s create a table named “Employees” with columns “First_Name” and “Last_Name”. Then populate the table with some fictitious data. Then view the results once and then interchange the values in the “First_Name” and “Last_Name” columns of the table. Then see the results in the table again to verify. The below T-SQL does all this:
Usually the UPDATE statement as in the above picture gives the impression of the both columns returning the same set of values (here Last_Name column values in both columns). But SQL Server behaves intelligent and interchanges the values in the two columns. It internally stores the values in a temporary column and then does the interchange.
Hope you enjoyed reading this post and found somewhat interesting learning. Keep coming back or follow this “Data Language” blog for more such interesting posts.
I have seen that comparing a datetime column in a table in SQL Server database, that has both date and time parts (e.g. 2012-12-05 10:37:36.897) with a date literal that has only date part is a not straight forward. Let me explain with an example:
Have created a temporary table with two date columns – From_Date and To_Date and inserted a couple of random date values. First output shows the table contents. Observe that the timestamps are also included with the date values.
Now declared a date variable and assigned a date value without timestamp. Then retrieved results from the table where From_Date is less than or equal to the date variable in two ways:
– once by converting the From_Date column into string taking the format “mm/dd/yyyy” (note the format style 101 in the convert function syntax) and then casting this date string back to datetime data type. This removes the timestamp from the datetime values.
– the other time by comparing the direct comparison between From_Date datetime column and datetime variable. This comparison compares the both date and time pieces of the From_Date column and the datetime variable, hence resulting in incorrect(less) number if results.
Hope that helps. Do leave your comments or questions, if any.. 🙂