Posts

Showing posts from April, 2012

Difference Between NEWSEQUENTIALID() and NEWID() - SQL Server

 both generates the GUID of datatype of uniqueidentifier NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order Example:- ----Create Test Table for with default columns values CREATE TABLE temptable ( Column1 uniqueidentifier DEFAULT NEWID (), Column2 uniqueidentifier DEFAULT NewSequentialID ()) ----Inserting five default values in table INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES INSERT INTO  temptable  DEFAULT VALUES SELECT *  FROM  temptable

Get Updated Tables & Procedures Names from Database - SQL Server

----how many days before you need declare @day int set @day=15 SELECT name FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < @day SELECT name FROM sys.objects WHERE type = 'U'  AND DATEDIFF(D,modify_date, GETDATE()) < @day SELECT name,type FROM sys.objects WHERE type = 'U' AND  DATEDIFF(D,modify_date, GETDATE()) < @day

Find First and Last Day of Current Month - SQL SERVER

DECLARE @mydate DATETIME SELECT @mydate = GETDATE () SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,-( DAY ( @mydate )), @mydate ), 101 ) ,  'Last Day of Previous Month' UNION SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,-( DAY ( @mydate )- 1 ), @mydate ), 101 ) AS Date_Value ,  'First Day of Current Month' AS Date_Type UNION SELECT CONVERT ( VARCHAR ( 25 ), @mydate , 101 ) AS Date_Value , 'Today' AS Date_Type UNION SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,-( DAY ( DATEADD ( mm , 1 , @mydate ))), DATEADD ( mm , 1 , @mydate )), 101 ) ,  'Last Day of Current Month' UNION SELECT CONVERT ( VARCHAR ( 25 ), DATEADD ( dd ,- DAY ( DATEADD ( mm , 1 , @mydate ))- 1 ), DATEADD ( mm , 1 , @mydate )), 101 ) , 'First Day of Next Month'

Find Last Day of Any Month date – Current Previous Next

----Last Day of Previous Month SELECT DATEADD ( s ,- 1 , DATEADD ( mm , DATEDIFF ( m , 0 , GETDATE ()), 0 )) as  LastDay_PreviousMonth ----Last Day of Current Month SELECT DATEADD ( s ,- 1 , DATEADD ( mm , DATEDIFF ( m , 0 , GETDATE ())+ 1 , 0 )) as  LastDay_CurrentMonth ----Last Day of Next Month SELECT DATEADD ( s ,- 1 , DATEADD ( mm , DATEDIFF ( m , 0 , GETDATE ())+ 2 , 0 )) as  LastDay_NextMonth ResultSet: LastDay_PreviousMonth ———————– 2012-03-31 10:59:59.000 LastDay_CurrentMonth ———————– 2012-04-30 10:59:59.000 LastDay_NextMonth ———————– 2007-05-31 23:59:59.000