Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Datetime Formates and Date time manipulations in SQL Server

May 19 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

Different Datetime formates in sqlserver

print getdate()  -- May 19 2009 3:21AM

 

print convert(varchar(max),getdate(),101) -- 05/19/2009

 

print convert(varchar(max),getdate(),102) -- 2009.05.19

 

print  convert(varchar(max),getdate(),103) -- 19/05/2009

 

print convert(varchar(max),getdate(),104) -- 19.05.2009

 

print convert(varchar(max),getdate(),105)  --  19-05-2009


print convert(varchar(max),getdate(),106) -- 19 May 2009

 

print convert(varchar(max),getdate(),107) -- May 19, 2009

 

print convert(varchar(max),getdate(),108) -- 03:21:06

 

print convert(varchar(max),getdate(),109) -- May 19 2009 3:21:06:283AM

 

print convert(varchar(max),getdate(),110) -- 05-19-2009

 

print convert(varchar(max),getdate(),111) -- 2009/05/19

 

print convert(varchar(max),getdate(),112) -- 20090519

 

print convert(varchar(max),getdate(),113) -- 19 May 2009 03:21:06:283

 

print convert(varchar(max),getdate(),114) -- 03:21:06:283

 

print convert(varchar(max),getdate(),120) -- 2009-05-19 03:21:06

 

print convert(varchar(max),getdate(),121) -- 2009-05-19 03:21:06.283

 

print convert(varchar(max),getdate(),127) -- 2009-05-19T03:21:06.283



--Get current Year  

  print year(getdate())

--Get current Month

  print month(getdate())

--Get current day  

  print day(getdate())

 

 

 

--datepart

print datepart(m,getdate())

print datepart(d,getdate())

print datepart(yy,getdate())

 

 

 

--date add

print dateadd(d,-1,getdate()) --get yesterday

print dateadd(d,1,getdate()) --get tomorrow

 

 

 

--Get end date of the month

print dateadd(d, -1,convert(datetime, convert(varchar,datepart(m,getdate())+1)+'/01/'+convert(varchar,datepart(yyyy,getdate()) )))


--Month Name

print DATENAME(month, GETDATE())


--Next year

print dbo.Date(year(getdate())+1, 1,1)

-- Previous Year
print dbo.Date(year(getdate())-1, 1,1)


--Get the all weeks of the year, i.e. Week start date and end date of the 53 weeks

declare @st datetime

declare @day varchar(10)

declare @nextdate int

declare @ws datetime

declare @we datetime

declare @enddate datetime

declare @date_int int

declare @weekid int


set @enddate = dbo.Date(year(getdate())+1, 1,1)

set @st = dbo.Date(year(getdate()), 1,1)

set @weekid = 1


select @day = datename(dw,@st)

 

create table #temp_weekdates(week_name varchar(10), week_sdate datetime,week_edate datetime)

while (@st < @enddate)

begin

   IF (@day = 'Tuesday')

        set @nextdate = 5

  ELSE

  IF(@day = 'MONDAY')

      set @nextdate = 6

  ELSE

  IF(@day = 'WEDNESDAY')

      set @nextdate = 4

   ELSE

  IF(@day = 'THURSDAY')

      set @nextdate = 3

  ELSE

  IF(@day = 'FRIDAY')

     set @nextdate = 2

  ELSE

  IF(@day = 'SATURDAY')

     set @nextdate = 1

  ELSE

  IF(@day = 'SUNDAY')

    set @nextdate = 7

 

  set @ws = @st

  set @we = dateadd(day,@nextdate,@st)

  set @date_int = convert(varchar(22),@we,112)

 

if (@date_int > convert(varchar(22),@enddate,112))

begin

declare @lastweekdays int

set @lastweekdays = 31 - day(@st)

set @we = dateadd(day,@lastweekdays,@st)

end

insert into #temp_weekdates values
   ('Week'+convert(varchar(2),@weekid),@WS,@WE)


set @st = dateadd(day,1,@we)

select @day = datename(dw,@st)

set @weekid = @weekid + 1


end


select * from #temp_weekdates

drop table #temp_weekdates

 

 

 

 




--All the Best

Share this post

Repost 0

Comment on this post

jose 08/05/2009 09:50

thanks buddyjust what i was looking for