How to format datetime & date in Sql ?

Introduction:

                  Lets see one by one with Example.

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
                                        – Jul  30 2012 11:01AM          
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 30/07/2012                  
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2012.30.07           
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                        – Oct  2 2012 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) – yyyymmdd
SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
                                        – 02 Oct 2012 11:02:07:577     
SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm
                                        – 2012-30-07T10:52:47.513
– SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy

Default values for a date parameter in SSRS

Introduction
                 We can use sql queries or some of the cool expressions available in SSRS to set default date parameters:


Set First Day of previous week (Monday)
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


Set Last Day of previous week (Sunday)
=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


Set First Day of Current Week (Monday)
=DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())


Set Last Day of Current Week (Sunday)
=DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today())


Set First Date of last month
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

Set Last date of last month
=DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))

Set First date of current month
=DateSerial(Year(Now()), Month(Now()), 1)

Set Last date of current month
=DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1))))