In Oracle there is a function (trunc) used to remove the time portion of a date. In order to do this with SQL Server, you need to use the convert function. Convert takes 3 parameters, the datatype to convert to, the value to convert, and an optional parameter for the formatting style. It is this third parameter we will use in order to customize the format of the date. — Month first SELECT CONVERT(VARCHAR(12),GETDATE(), 101) – 06/29/2009 SELECT CONVERT(VARCHAR(12),GETDATE(), 110) – 06-29-2009 SELECT CONVERT(VARCHAR(12),GETDATE(), 100) – Jun 29 2009 SELECT CONVERT(VARCHAR(12),GETDATE(), 107) – Jun 29, 2009 — Year first SELECT CONVERT(VARCHAR(12),GETDATE(), 102) – 2009.06.29 SELECT CONVERT(VARCHAR(12),GETDATE(), 111) – 2009/06/29 SELECT CONVERT(VARCHAR(12),GETDATE(), 112) – 20090629 — Day first SELECT CONVERT(VARCHAR(12),GETDATE(), 103) – 29/06/2009 SELECT CONVERT(VARCHAR(12),GETDATE(), 105) – 29-06-2009 SELECT CONVERT(VARCHAR(12),GETDATE(), 104) – 29.06.2009 SELECT CONVERT(VARCHAR(12),GETDATE(), 106) – 29 Jun 2009 — Time only SELECT CONVERT(VARCHAR(12),GETDATE(), 108) – 07:26:16 SELECT CONVERT(VARCHAR(12),GETDATE(), 114) – 07:27:11:203 — Date Only No Time (SQL 2008) [thank you John] SELECT CAST(GetDate() AS DATE); – 08/12/2011
Continue reading ...
Prior to SQL Server 2005, there was no inherent function to generate a rownumber within a row. There is a workaround however for SQL 2000. If you are on SQL 2005+, then you will utilize the following function: Method 1 — SQL 2005+ SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY c.CustomerID ASC) ,c.* FROM SalesLT.Customer c Method 2 There are two ways to do this in SQL 2000. The easiest way is to join the table on itself and count the number of rows that precede the primary key. — SQL 2000+ SELECT Rownum = ( SELECT COUNT(1) FROM SalesLT.Customer WHERE CustomerID <= c.CustomerID ), * FROM SalesLT.Customer c ORDER BY CustomerID Method 3 The other way for SQL 2000 is to use the IDENTITY function in order to generate a Rownum. The downside is that you must insert the results into a temp table, then select from the temp table in order to retrieve the rownum. — SQL 2000+ SELECT RowNumber = IDENTITY(INT,1,1) ,c.LastName ,c.FirstName INTO #Customer_RowID FROM SalesLT.Customer c ORDER BY c.LastName ASC SELECT * FROM #Customer_RowID Also notice from this example that when we insert the results into the temp table, we are specifying the column names. This is because the identity function will not work if you include the primary key of the table, which is another downside.
Continue reading ...
Many Oracle developers trying to find the SQL Server function compatible with their Contains clause will most likely end up on this page. Therefore, this page title is directed towards the Oracle developer rather than for the SQL Server’s Contains function which is used for full-text searching. The most similar function to Oracle’s contains is charindex. The usage is similar except the first two parameters are reversed: DECLARE @BaseString VARCHAR(MAX) SET @BaseString = ‘Quick Brown Fox’ SELECT CHARINDEX(’Brown’, @BaseString, 1) The result returns the integer value 7, indicating the character position for the string ‘grand’ within the @BaseString variable. The last parameter allows a start position to be specified.
Continue reading ...
