SQL Date Function

Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.


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'


First Day of Next Month


DECLARE @datum DATETIME
SET @datum=GETDATE()

DECLARE @datFuture DATETIME
SET @datFuture = dateadd(mm,1,@datum)

DECLARE @dateFirstDayNewMonth datetime
SET @dateFirstDayNewMonth = cast(year(@datFuture) as varchar(50)) + '-' + cast(month(@datFuture) as varchar(50)) + '-1'

print @dateFirstDayNewMonth


OR


DECLARE @datum DATETIME
SET @datum=getdate()
select dateadd(month,datediff(month,0,@datum)+1,0)


print all the months between 2 dates, eg. 01 Aug 2006 and 31 March 2007


declare @date1 datetime,@date2 datetime
select @date1='01 Aug 2006', @date2 ='31 mar 2007'

select dateadd(month,number,@date1) from master..spt_values
where type='p' and number between 0 and datediff(month,@date1,@date2)


OR


DECLARE @date1 DATETIME,
@date2 DATETIME

SELECT @date1 = '01 Aug 2006', @date2 = '31 mar 2007';

WITH months(monthnames)
AS (SELECT @date1 AS monthnames
UNION ALL
SELECT DATEADD(mm,1,monthnames)
FROM months
WHERE monthnames < @date2) SELECT STUFF(CONVERT(VARCHAR(12),monthnames,106),1,3,'') AS monthname FROM months OPTION (MAXRECURSION 0) first date of last month


select cast(cast(datepart(month, dateadd(mm, -1, getdate())) as varchar) +
'/' +
cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))
as varchar) + '/' +
cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)


This week start date Monday


select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)


This week enddate


select DATEADD(wk, DATEDIFF(wk,-7,getdate()),-1)


Last week start date


select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)


Last week end date


select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)


First day of this year


select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)


Last day of the prior year


select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)


First day of next year


select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)


First day of prior year


SELECT DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)


This week last Year start date Monday


select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)


This day last year


select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))


First day of the current month


select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)


Last day of the current month


select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)


First day of the current month Last year


select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))


Last day of the current month Last Year


select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)

Delete Dulicate Records

Let's create some duplicate records. Then we'll delete duplicate records.

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO