Formatar Datas em SQL

SET DATEFORMAT mdyGODECLARE @datevar datetimeSET @datevar = '12/31/98'SELECT @datevarGO
SET DATEFORMAT ydmGODECLARE @datevar datetimeSET @datevar = '98/31/12'SELECT @datevarGO
SET DATEFORMAT ymdGODECLARE @datevar datetimeSET @datevar = '98/12/31'SELECT @datevarGO

Style ID Style Type 0 or 100 mon dd yyyy hh:miAM (or PM) 101 mm/dd/yy 102 yy.mm.dd 103 dd/mm/yy 104 dd.mm.yy 105 dd-mm-yy 106 dd mon yy 107 Mon dd, yy 108 hh:mm:ss 9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) 110 mm-dd-yy 111 yy/mm/dd 112 yymmdd 13 or 113 dd mon yyyy hh:mm:ss:mmm(24h) 114 hh:mi:ss:mmm(24h) 20 or 120 yyyy-mm-dd hh:mi:ss(24h) 21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h) 126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces) 130 dd mon yyyy hh:mi:ss:mmmAM 131 dd/mm/yy hh:mi:ss:mmmAM

declare @datevar datetime select @datevar = getdate()
/*Example for getdate() : getting current datetime*/ select getdate() [Current Datetime]
/*Example for dateadd : getting date 7 days from current datetime*/select dateadd(dd, 7, @datevar) [Date 7 days from now]
/*Example for datediff : getting no of days passed since 01-01-2004*/select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]
/*Example for datename : getting month name*/select datename(mm, @datevar) [Month Name]
/*Example for datepart : getting week from date*/select datepart(wk, @datevar ) [Week No]
/*Example for day : getting day part of date*/select day (@datevar) [Day]
/*Example for month : getting month part of date*/select month(@datevar) [Month]
/*Example for year : getting year part of date*/select year(@datevar) [Year]

A seguir apresento alguns códigos de exemplo:

1. Achar o 1º dia do Mês:
select dateadd(dd,-(day(DateColumn)-1),DateColumn)
2. O último dia do Mês:
select dateadd(dd,-(day(dateadd(mm,1,DateColumn))),dateadd(mm,1,DateColumn))