|
Converting and formatting DateTimes in SQL queries |
||||||||
I've come across the need to format a dateTime as a string in SQL, so after a little research I found that its pretty straightforward.
I want to select the dateTime, and return it as a string (to a Flex dataObject). I also want to be able to control the return formatting. You could do this easily enough in coldfusion, but ideally we'll let SQL Server handle it.
1Select fields, Convert(varchar(20), dateField, 103)
2From Table
2From Table
Or
1Select fields, Cast(DTStart As Varchar(20))
2From Table
2From Table
I'm preferring the first option, as I managed to find the reference table for the format masks specified in that example.
To change the format of the date, you convert the requested date to a string and set the format by passing an encoding character string. Below is a list of the codes, and sample outputs.
1select convert(varchar, getdate(), 1) 07/04/08
2select convert(varchar, getdate(), 2) 08.07.04
3select convert(varchar, getdate(), 3) 04/07/08
4select convert(varchar, getdate(), 4) 04.07.08
5select convert(varchar, getdate(), 5) 04-07-08
6select convert(varchar, getdate(), 6) 04 Jun 08
7select convert(varchar, getdate(), 7) Jun 04, 08
8select convert(varchar, getdate(), 10) 07-04-08
9select convert(varchar, getdate(), 11) 08/07/04
10select convert(varchar, getdate(), 101) 07/04/2008
11select convert(varchar, getdate(), 102) 2008.07.04
12select convert(varchar, getdate(), 103) 04/07/2008
13select convert(varchar, getdate(), 104) 04.07.2008
14select convert(varchar, getdate(), 105) 04-07-2008
15select convert(varchar, getdate(), 106) 04 Jun 2008
16select convert(varchar, getdate(), 107) Jun 04, 2008
17select convert(varchar, getdate(), 110) 07-04-2008
18select convert(varchar, getdate(), 111) 2008/07/04
2select convert(varchar, getdate(), 2) 08.07.04
3select convert(varchar, getdate(), 3) 04/07/08
4select convert(varchar, getdate(), 4) 04.07.08
5select convert(varchar, getdate(), 5) 04-07-08
6select convert(varchar, getdate(), 6) 04 Jun 08
7select convert(varchar, getdate(), 7) Jun 04, 08
8select convert(varchar, getdate(), 10) 07-04-08
9select convert(varchar, getdate(), 11) 08/07/04
10select convert(varchar, getdate(), 101) 07/04/2008
11select convert(varchar, getdate(), 102) 2008.07.04
12select convert(varchar, getdate(), 103) 04/07/2008
13select convert(varchar, getdate(), 104) 04.07.2008
14select convert(varchar, getdate(), 105) 04-07-2008
15select convert(varchar, getdate(), 106) 04 Jun 2008
16select convert(varchar, getdate(), 107) Jun 04, 2008
17select convert(varchar, getdate(), 110) 07-04-2008
18select convert(varchar, getdate(), 111) 2008/07/04
TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
[Subscribe to Comments]
Assignment and essay writing are the rigorous and inclusive in all terms and endeavors. The scheme and http://pay4essayonline.blogspot.com/ organization of the schools and colleges is upheld and implemented.
# Posted By Alice Martin Joe
| 25/11/2015 03:10
[Add Comment]
