[SQL] Date Functions

Syntax
DATEPART ( datepart , date )

Day of the Week
SELECT DATEPART(dw,GETDATE())

Year
SELECT DATEPART(yy,GETDATE())
SELECT DATEPART(yyyy,GETDATE())

Quarter
SELECT DATEPART(q,GETDATE())
SELECT DATEPART(qq,GETDATE())

Month
SELECT DATEPART(m,GETDATE())
SELECT DATEPART(mm,GETDATE())

Day of the Year
SELECT DATEPART(y,GETDATE())
SELECT DATEPART(dy,GETDATE())

Month Day
SELECT DATEPART(d,GETDATE())
SELECT DATEPART(dd,GETDATE())

Week
SELECT DATEPART(wk,GETDATE())
SELECT DATEPART(ww,GETDATE())

Hour
SELECT DATEPART(hh,GETDATE())

Minute
SELECT DATEPART(n,GETDATE())
SELECT DATEPART(mi,GETDATE())

Second
SELECT DATEPART(s,GETDATE())
SELECT DATEPART(ss,GETDATE())

Millisecond
SELECT DATEPART(ms,GETDATE())

[SQL] Convert VARCHAR To VARBINARY

Usefull for hinding a value.

DECLARE @Password varchar(50)

SET @Password = 'MLAV'

SELECT CONVERT(varbinary(50), @Password)

[SQL] Get Number of Days in a Month Function

REATE FUNCTION [dbo].[GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN

   RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
               WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
               ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                               YEAR(@pDate) % 100 != 0) OR
                              (YEAR(@pDate) % 400  = 0)
                         THEN 29
                         ELSE 28
                    END
          END

END
GO

[SQL] Measuring the time span between two dates

SELECT DATEDIFF(dd, '1/1/01', GETDATE())
GO

[SQL] Retrieves the current date

SELECT GETDATE()
GO

[SQL] Sample Complex Search - StoredProcedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Service.CPIC.GetFiles]
(
@PoliceDeptId int = NULL,
@FileType int = NULL,
@PoliceOfficerId int = NULL,
@FirstName nvarchar(50) = NULL,
@LastName nvarchar(50) = NULL,
@StartFileCreationDate datetime = NULL,
@EndFileCreationDate datetime = NULL,
@StartFileCompletionDate datetime = NULL,
@EndFileCompletionDate datetime = NULL
)
AS

DECLARE @MainQuery nvarchar(MAX)
DECLARE @Condition nvarchar(MAX)

SET NOCOUNT ON;

SET @Condition = ''
-- Main Query
SET @MainQuery = '
SELECT
[CPICId],
[File_ServiceId],
[CandidateId],
[ResultsId],
[IsRushJob],
[PositionOfTrust],
[AccurateRepresentation],
[CriminalRecords],
[FirstName],
[LastName],
[MiddleName],
[MaidenName],
[Aliases],
[Gender],
[DateOfBirth],
[BirthCity],
[Height],
[Weight],
[EyeColor],
[HairColor],
[Address],
[PreviousAddress],
[Status],
[Owner],
[PoliceDeptId],
[CheckedDateTime],
[CheckedByPoliceOfficerId],
[ConfirmedDateTime],
[ConfirmedByPoliceOfficerId],
[CheckOutById],
[FileType],
[DateTimeCreated],
[DateTimeCompleted]
FROM [dbo].[Service.CPIC.GetAllFiles]()
'


-- @PoliceDeptId
IF (@PoliceDeptId IS NOT NULL)
BEGIN
SET @Condition = 'WHERE [PoliceDeptId] = ' + CAST(@PoliceDeptId as nvarchar(max)) + ' '
END

-- @FileType
IF (@FileType IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [FileType] = ' + CAST(@FileType as nvarchar(max)) + ' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [FileType] = ' + CAST(@FileType as nvarchar(max)) + ' '
END
END

-- @PoliceOfficerId
IF (@PoliceOfficerId IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [CheckedByPoliceOfficerId] = ' + CAST(@PoliceOfficerId as nvarchar(max)) + ' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [CheckedByPoliceOfficerId] = ' + CAST(@PoliceOfficerId as nvarchar(max)) + ' '
END
END

-- @FirstName
IF (@FirstName IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [FirstName] LIKE ''' + @FirstName + '%'' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [FirstName] LIKE ''' + @FirstName + '%'' '
END
END

-- @LastName
IF (@LastName IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [LastName] LIKE ''' + @LastName + '%'' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [LastName] LIKE ''' + @LastName + '%'' '
END
END

-- @StartFileCreationDate AND @EndFileCreationDate
IF ((@StartFileCreationDate IS NOT NULL) AND (@EndFileCreationDate IS NOT NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCreationDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCreationDate,101) + ''' as datetime) '
END
END

IF ((@StartFileCreationDate IS NOT NULL) AND (@EndFileCreationDate IS NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) '
END
END

-- @StartFileCompletionDate AND @EndFileCompletionDate
IF ((@StartFileCompletionDate IS NOT NULL) AND (@EndFileCompletionDate IS NOT NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCompletionDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCompletionDate,101) + ''' as datetime) '
END
END

IF ((@StartFileCompletionDate IS NOT NULL) AND (@EndFileCompletionDate IS NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) '
END
END

PRINT @MainQuery + ' ' + @Condition
EXEC (@MainQuery + ' ' + @Condition)


/*
SELECT *
FROM [dbo].[Service.CPIC.GetAllFiles]()

-- @PoliceDeptId
WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)

-- @StartFileCreationDate
AND [


-- @EndFileCreationDate
*/

[SQL] Conversion DateTime DataType

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example:

Format Date to mm/dd/yyyy
CONVERT(nvarchar(11),[FieldName],101)

From: '2007-05-04 16:35:52.140'
To: '05/04/2007'

Reseting Time Value to Zero
CAST(CONVERT(nvarchar(11),[FieldName],101) as datetime)

From: '2007-05-04 16:35:52.140'
To: '2007-05-04 00:00:00.000'

[SQL] Common Catalog Stored Procedures

1. sp_columns
Returns column information for the specified tables or views that can be queried in the current environment.

Example:

USE AdventureWorks
GO
EXEC sp_columns @table_name = N'Department',
@table_owner = N'HumanResources'

2. sp_tables
Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause.

Example:
USE AdventureWorks;GOEXEC sp_tables @table_name = '%', @table_owner = 'Person', @table_qualifier = 'AdventureWorks'

3. sp_databases
Lists databases that either reside in an instance of the SQL Server 2005 Database Engine or are accessible through a database gateway.

Example:
USE master;
GO
EXEC sp_databases;

[SQL] Complex WHERE Condition

WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)