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