Database Simplified Headline Animator

Database Simplified

Tuesday, 30 August 2011

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

   

No comments:

Post a comment