Pages

Sep 22, 2008

Useful - Date Fucntions in SQL SERVER 2005


Here are some of the Most useful DateTime Functions that used to reduce many lines of code.

—-Today

SELECT GETDATE() ‘Today’

—-Yesterday

SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’

—-First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’

—-Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’

—-First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’

—-Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’

—-First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’

—-Last Day of Current Month

SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’

—-First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’

—-Last Day of Last Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

‘Last Day of Last Month’

—-First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’

—-Last Day of Current Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

'Last Day of Current Year’

—-First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’

—-Last Day of Last Year

SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

‘Last Day of Last Year’

REF:http://blog.sqlauthority.com/2008/08/29/sql-server-few-useful-datetime-functions-to-find-specific-dates/

Sep 15, 2008

Searching a column name using Sounds in SQL SERVER 2005

SQL SERVER 2005 Even adds a Advanced search option like Searching using Sounds.
For eg:
We can search using the sounds of the word say, Column name having the sounds like rich..using the SOUNDEX Function May contain
Name:
Rex

Rich
Relay
Ross
Raj

The Query looks like

SELECT COLUMN_NAME FORM [dbo].[TABLE_NAMEWHERE]
WHERE SOUNDEX(COLUMN_NAME) = SOUNDEX('Rich')

This will return the column values which sound like rich.. We cant do this using 'like' Operator

How to find the the Primary Key columns in a DB in SQL SERVER 2005

How to List Primary Key columns in a DB:
This the Query that ll list all the Primary Key columns with its table name and column name in a DataBase,

SELECT p.TABLE_NAME,c.CONSTRAINT_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = p.TABLE_NAMEAND
AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
ORDER by c.TABLE_NAME

Sep 4, 2008

Automatically Running Stored Procedures at SQL Server Startup

Here in the below link u can find a very simple implementation of Automatically running SPs at SQL SERVER startup.
http://www.mssqltips.com/tip.asp?tip=1574