Problems faced when dealing with DateTime field
Handling DateTime fields is sometimes annoying for beginners. The reason is the stubborn DateTime field. Chk this out;
Select [yourDateTimeField] From [TableName]
e.g. SELECT PostTime FROM ddl_log (select master first)
As you can see, time string always comes with the date. This thing gets even more irritating when we want date in its simplest most common form.
The point is, how to get hold of this DatType and mould it in our desired shape?
Lets take it step by step. First you fetch datetime from Sql and you get a value like '2009-12-10 23:18:44.923'. Question arrises;
Is there any ONLY DATE field in SQL?
The answer is no. You might be able to make a custom field using User-defined Data Types feature of Sql Server. In case if you dont do that, we come to the next step;
What do I do for the existing date fields that come with additional and unrequired time values
The answer is you Format them.
T-SQL gives rich string formating, at the cost of loosing original data type (but no loss of value)
Try this
Select Convert(Varchar, getDate(), 101)
What happened?? Isn't that something you just needed for which you looked so hard and finally came to this blog lolz.
What happened is that you see a date with the desired output. You can put your datetime field name in place of 'getDate()'
This function is tremendous to get the date in your desired format. Run the following queries and observe the changes;
Select Convert(Varchar, getDate(), 110)
Select Convert(Varchar, getDate(), 113)
Select Convert(Varchar, getDate(), 112)
Theres more to get as we share with you all the parameters that changes the datetime field in various usefull formats;
Date Time conversaion Table
1 |
Select Convert(Varchar, getDate(), 0)
|
Dec 24 2009 10:19PM
|
2 |
Select Convert(Varchar, getDate(), 1) |
112/24/09
|
3 |
Select Convert(Varchar, getDate(), 2) |
009.12.24
|
4 |
Select Convert(Varchar, getDate(), 3) |
224/12/09
|
5 |
Select Convert(Varchar, getDate(), 4) |
224.12.09
|
6 |
Select Convert(Varchar, getDate(), 5) |
224-12-09
|
7 |
Select Convert(Varchar, getDate(), 6) |
224 Dec 09
|
8 |
Select Convert(Varchar, getDate(), 7) |
DDec 24, 09
|
9 |
Select Convert(Varchar, getDate(), 8) |
222:19:31
|
10 |
Select Convert(Varchar, getDate(), 9) |
DDec 24 2009 10:19:31:203PM
|
11 |
Select Convert(Varchar, getDate(), 10) |
112-24-09
|
12 |
Select Convert(Varchar, getDate(), 11) |
009/12/24
|
13 |
Select Convert(Varchar, getDate(), 12) |
0091224
|
14 |
Select Convert(Varchar, getDate(), 13) |
224 Dec 2009 22:19:31:203
|
15 |
Select Convert(Varchar, getDate(), 14)
|
22:19:31:203
|
16 |
Select Convert(Varchar, getDate(), 20) |
22009-12-24 22:19:31
|
17 |
Select Convert(Varchar, getDate(), 21) |
22009-12-24 22:19:31.203
|
18 |
Select Convert(Varchar, getDate(), 22) |
112/24/09 10:19:31 PM
|
19 |
Select Convert(Varchar, getDate(), 23) |
22009-12-24
|
20 |
Select Convert(Varchar, getDate(), 24) |
222:19:31
|
21 |
Select Convert(Varchar, getDate(), 25) |
22009-12-24 22:19:31.203
|
22 |
Select Convert(Varchar, getDate(), 100) |
DDec 24 2009 10:19PM
|
23 |
Select Convert(Varchar, getDate(), 101) |
112/24/2009
|
24 |
Select Convert(Varchar, getDate(), 102) |
22009.12.24
|
25 |
Select Convert(Varchar, getDate(), 103) |
224/12/2009
|
26 |
Select Convert(Varchar, getDate(), 104) |
224.12.2009
|
27 |
Select Convert(Varchar, getDate(), 105) |
224-12-2009
|
28 |
Select Convert(Varchar, getDate(), 106) |
224 Dec 2009
|
29 |
Select Convert(Varchar, getDate(), 107) |
DDec 24, 2009
|
30 |
Select Convert(Varchar, getDate(), 108) |
222:19:31
|
31 |
Select Convert(Varchar, getDate(), 109) |
DDec 24 2009 10:19:31:207PM
|
32 |
Select Convert(Varchar, getDate(), 110) |
112-24-2009
|
33 |
Select Convert(Varchar, getDate(), 111) |
22009/12/24
|
34 |
Select Convert(Varchar, getDate(), 112) |
220091224
|
35 |
Select Convert(Varchar, getDate(), 113) |
224 Dec 2009 22:19:31:207
|
36 |
Select Convert(Varchar, getDate(), 114) |
22:19:31:207
|
by
