DECLARE: apply value in multiple places

 What if you need to specify a value such as date in multiple places in a query? Try DECLARE The following example using DECLARE so that you only need to change the date range at one place it will apply to both order date and ship date statements in the WHERE clause below.

DECLARE @qstartdate date, @qenddate date
            SET @qstartdate = '2012-01-01'
            SET @qenddate = '2012-01-31'

SELECT *
FROM dbo.ORDER_ENTRY order

WHERE
order.ORDER_DATE BETWEEN CAST(@qstartdate as DATE) AND CAST(@qenddate as DATE)

OR 

order.SHIPPING_DATE BETWEEN CAST(@qstartdate as DATE) AND CAST(@qenddate as DATE)

Spliting a Full Name data into First Name, Last Name columns

Quite often when working with a old database where a single column been used to store both first name and last name. Now we have to separate them into two columns.

The following code is an example of how to split a full name columns into first name and last name columns. 


SELECT

LEFT (EMPLOYEE_FULL_NAME, CHARINDEX(' ',EMPLOYEE_FULL_NAME))as 'Employee First Name' 

,SUBSTRING (EMPLOYEE_FULL_NAME, CHARINDEX(' ',EMPLOYEE_FULL_NAME)+1, LEN(EMPLOYEE_FULL_NAME)-(CHARINDEX(' ',EMPLOYEE_FULL_NAME)-1 ))

 as 'Employee Last Name'

 

FROM [dbo].[HR_Employee_table]