0 | Feb 22 2006 4:26PM | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0) |
1 | 02/22/06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1) |
2 | 06.02.22 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2) |
3 | 22/02/06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3) |
4 | 22.02.06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4) |
5 | 22-02-06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5) |
6 | 22 Feb 06 | CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6) |
7 | Feb 22, 06 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7) |
8 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8) |
9 | Feb 22 2006 4:26:08:020PM | CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9) |
10 | 02-22-06 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10) |
11 | 06/02/22 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11) |
12 | 060222 | CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12) |
13 | 22 Feb 2006 16:26:08:020 | CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13) |
14 | 16:26:08:037 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14) |
20 | 2006-02-22 16:26:08 | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20) |
21 | 2006-02-22 16:26:08.037 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21) |
22 | 02/22/06 4:26:08 PM | CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22) |
23 | 2006-02-22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23) |
24 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24) |
25 | 2006-02-22 16:26:08.037 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25) |
100 | Feb 22 2006 4:26PM | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100) |
101 | 02/22/2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101) |
102 | 2006.02.22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102) |
103 | 22/02/2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103) |
104 | 22.02.2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104) |
105 | 22-02-2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105) |
106 | 22 Feb 2006 | CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106) |
107 | Feb 22, 2006 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107) |
108 | 16:26:08 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108) |
109 | Feb 22 2006 4:26:08:067PM | CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109) |
110 | 02-22-2006 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110) |
111 | 2006/02/22 | CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111) |
112 | 20060222 | CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) |
113 | 22 Feb 2006 16:26:08:067 | CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113) |
114 | 16:26:08:067 | CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114) |
120 | 2006-02-22 16:26:08 | CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120) |
121 | 2006-02-22 16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121) |
126 | 2006-02-22T16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126) |
127 | 2006-02-22T16:26:08.080 | CONVERT(CHAR(23), CURRENT_TIMESTAMP, 127) |
130 | 24 ???? 1427 4:26:08:080PM | CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130) |
131 | 24/01/1427 4:26:08:080PM | CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131) |
如数据:
MonitorTime
2012-05-05 00:00:00.0002012-05-05 16:00:00.0002012-05-05 17:00:00.0002012-05-05 18:00:00.0002012-05-05 19:00:00.000 select CONVERT(CHAR(19), MonitorTime, 120) from t_metData
执行结果:
(无列名)
2012-05-05 00:00:002012-05-05 16:00:002012-05-05 17:00:00
我们在处理字符串日期格式常用DateTime.Pares() 但是这个形式的转换是相当有限的,有些C#是会不懂你写入的日期格式的如20031231。那么类似 "20100101" 或者其它形式的字符串如何转换成日期型呢?
一、拼接字符串的形式
DateTime dt=Convert.ToDateTime("20100101".Substring(0,4)+"-"+"20100101".Substring(4,2)+"-"+"20071107".Substring(6,2));
二、Convert.ToDateTime(string)
string格式有要求,必须是yyyy-MM-dd hh:mm:ss
三、Convert.ToDateTime(string, IFormatProvider)
DateTime dt;
DateTimeFormatInfo dtFormat = new System.GlobalizationDateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
dt = Convert.ToDateTime("2011/05/26", dtFormat);
四、DateTime.ParseExact()
string dateString = "20110526";
DateTime dt = DateTime.ParseExact(dateString, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
//或者
DateTime dt = DateTime.ParseExact(dateString, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
五、DateTime.TryParse(string,out datetime)
1、更多时候,会采用DateTime.TryParse(string,out datetime)方法,因为此方法有安全机制,当string内容不正确时,可以返回日期的最小值MinValue。并且可以通过返回的bool值判断转化是否成功。而DateTime.ParseExact()需要按特定的格式来转换,对格式的要求比较严,如果string中不是日期内容,而量类似“asdfasd”的字符串,则会出错。
2、用DateTime.TryParse(string,out datetime)转换后,得到的datetime可以用 datetime.ToString("ddd, MMM. dd")来转换为特殊需求的格式,比较灵活方便。