Is any date function like last day, first day (SQL server)?

• zdenek douda

  SSC-Addicted

  Points: 419

  More actions

  August 30, 2004 at 2:44 am

  #162009

  Thanks Zd.

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  August 30, 2004 at 4:12 am

  #521021

  --FirstDayofMonth

  select datename(dw,CONVERT(DATETIME, CONVERT(VARCHAR,datepart(M,getdate())) + '/1/' + CONVERT(VARCHAR, datepart(YYYY,getdate()))))

  --LastDayOfMonth

  select datename(dw,dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01')))

  Thanks,

  Ganesh

• zdenek douda

  SSC-Addicted

  Points: 419

  More actions

  August 30, 2004 at 5:13 am

  #521024

  Great,

  and which day is last of mont (31.1., 28.2., .......), but no datename.

  thanks

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  August 30, 2004 at 5:20 am

  #521025

  To get the last day of a month one can use something like this

  SELECT

  DATEADD(DAY, -DAY(GETDATE()), CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME))

  AS Monatsultimo

  However, using integer operations should be faster:

  SELECT DATEADD(d,DATEDIFF(d,DAY(GETDATE()),GETDATE()),0) AS Monatsultimo

  A pretty cool script comes from SQL Server MVP Steve Kass:

  declare @31st datetime

  set @31st = '19341031' -- any 31st

  declare @now datetime

  set @now = getdate()

  select dateadd(month,datediff(month,@31st,@now),@31st)

  Just make sure, your starting date is any valid 31st, no matter whether in the past or future.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]