How to Get Last And First Date Of Current Month And Working with Different Date Time Formats

In This Blog We’ll see the different date time formats and how to get desirable format with simple Query.

Declare @DateNow DateTime
Set @DateNow=GETDATE()

--Current DateTime

Select @DateNow As [Now]

--Last Day Of Previous Month

SElect @DateNow-DAY(@DateNow) AS [Last Day Of Previous Month]

--First Day Of Current Month

Select @DateNow-DAY(@DateNow)+1 AS [First Day Of Current Month]

--Last Day Of Current Month

Select DateAdd(mm,1,@DateNow)-DAY(@DateNow) As [Last Day Of Current Month]

--Extrating WeekDay Name, Month Name, Year

Select DATENAME(dw,@DateNow) [WeekDay Name],DATENAME(mm,@DateNow) [Name Of Month],MONTH(@DateNow) [Month],YEAR(@DateNow) [Year],DAY(@DateNow) [Day]

--Extracting Time

Select Right(Convert(Varchar,@DateNow,100),7) [Current Time]

How to Format A Date ? See Following

Lets Say You Want Get Date Format = “dd/mm/yyyy”

As You’ve already seen GetDate() Function Returns = ‘2011-08-29 20:33:07.040’ Format

Let’s See How to Convert Getdate() returned format to desirable Format with a simple query, there are many format code provided by SQL Server. We'll Look at those format as well.

So To Convert DateTime To A Desirable Format Use Following Syntax

Convert(Varchar(30),[DateTimeToConvert],[Target FormatCode])

Following is the list of formats.
To achieve above format “dd/mm/yyyy” we wil use highlighted format which is “103”

Ok, Now Use The Above Syntax To Get Desired Format which is “103”

Select Convert(Varchar(10),GetDate(),103)

So with the small query and with the help of format provided you can achieve any format

Format Code	Format	Format Code	Format
1	08/29/11	100	Aug 29 2011 8:15PM
2	11.08.29	101	08/29/2011
3	29/08/11	102	2011.08.29
4	29.08.11	103	29/08/2011
5	29-08-11	104	29.08.2011
6	29 Aug 11	105	29-08-2011
7	Aug 29, 11	106	29 Aug 2011
8	20:15:13	107	Aug 29, 2011
9	Aug 29 2011 8:15:13:180PM	108	20:15:13
10	08-29-11	109	Aug 29 2011 8:15:13:180PM
11	11/08/29	110	08-29-2011
12	110829	111	2011/08/29
13	29 Aug 2011 20:15:13:180	112	20110829
14	20:15:13:180	113	29 Aug 2011 20:15:13:180
20	2011-08-29 20:15:13	114	20:15:13:180
21	2011-08-29 20:15:13.180
22	08/29/11 8:15:13 PM
23	2011-08-29
24	20:15:13
25	2011-08-29 20:15:13.180