SQL 时间格式变换

SELECT CONVERT(varchar(100), cdate, 0):24 06 2009 21:57PM
SELECT CONVERT(varchar(100), cdate, 1): 06/24/09
SELECT CONVERT(varchar(100), cdate, 2): 09.06.24
SELECT CONVERT(varchar(100), cdate, 3): 24/06/09
SELECT CONVERT(varchar(100), cdate, 4): 24.06.09
SELECT CONVERT(varchar(100), cdate, 5): 24-06-09
SELECT CONVERT(varchar(100), cdate, 6): 24 06 09
SELECT CONVERT(varchar(100), cdate, 7): 06 24, 09
SELECT CONVERT(varchar(100), cdate,: 10:57:46
SELECT CONVERT(varchar(100), cdate, 9): 06 24 2009 21:57:46:827AM
SELECT CONVERT(varchar(100), cdate, 10): 06-24-09
SELECT CONVERT(varchar(100), cdate, 11): 09/06/24
SELECT CONVERT(varchar(100), cdate, 12): 090624
SELECT CONVERT(varchar(100), cdate, 13): 24 06 2009 21:57:46:937
SELECT CONVERT(varchar(100), cdate, 14): 10:57:46:967
SELECT CONVERT(varchar(100), cdate, 20): 2009-06-24 21:57:47
SELECT CONVERT(varchar(100), cdate, 21): 2009-06-24 21:57:47.157
SELECT CONVERT(varchar(100), cdate, 22): 06/24/09 21:57:47 PM
SELECT CONVERT(varchar(100), cdate, 23): 2009-06-24
SELECT CONVERT(varchar(100), cdate, 24): 10:57:47
SELECT CONVERT(varchar(100), cdate, 25): 2009-06-24 21:57:47.250
SELECT CONVERT(varchar(100), cdate, 100): 06 24 2009 21:57PM
SELECT CONVERT(varchar(100), cdate, 101): 06/24/2009
SELECT CONVERT(varchar(100), cdate, 102): 2009.06.24
SELECT CONVERT(varchar(100), cdate, 103): 24/06/2009
SELECT CONVERT(varchar(100), cdate, 104): 24.06.2009
SELECT CONVERT(varchar(100), cdate, 105): 24-06-2009
SELECT CONVERT(varchar(100), cdate, 106): 24 06 2009
SELECT CONVERT(varchar(100), cdate, 107): 06 24, 2009
SELECT CONVERT(varchar(100), cdate, 108): 10:57:49
SELECT CONVERT(varchar(100), cdate, 109): 06 24 2009 21:57:49:437PM
SELECT CONVERT(varchar(100), cdate, 110): 06-24-2009
SELECT CONVERT(varchar(100), cdate, 111): 2009/06/24
SELECT CONVERT(varchar(100), cdate, 112): 20090624
SELECT CONVERT(varchar(100), cdate, 113): 24 06 2009 21:57:49:513
SELECT CONVERT(varchar(100), cdate, 114): 10:57:49:547
SELECT CONVERT(varchar(100), cdate, 120): 2009-06-24 21:57:49

根据这些就可以把时间转换成你想要的格式、



---------------------利用SQL语句获取本周、本月数据-----------------------------------------
本周：select * from table where datediff(week,C_CALLTIME,getdate())=0???? --C_CALLTIME 为日期字段

本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0?? --C_CALLTIME 为日期字段

本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0

前半年1-6，后半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 = datepart(mm,getdate())/7

?

?

-----------------------------------------------------------

1.??当前系统日期、时间?
????select?getdate()??

2.?dateadd??在向指定日期加上一段时间的基础上，返回新的?datetime?值
???例如：向日期加上2天?
???select?dateadd(day,2,'2004-10-15')??--返回：2004-10-17?00:00:00.000?

3.?datediff?返回跨两个指定日期的日期和时间边界数。
???select?datediff(day,'2004-09-01','2004-09-18')???--返回：17

4.?datepart?返回代表指定日期的指定日期部分的整数。
??select?DATEPART(month,?'2004-10-15')??--返回?10

5.?datename?返回代表指定日期的指定日期部分的字符串
???select?datename(weekday,?'2004-10-15')??--返回：星期五

6.?day(),?month(),year()?--可以与datepart对照一下

select?当前日期=convert(varchar(10),getdate(),120)?
,当前时间=convert(varchar(8),getdate(),114)?

select?datename(dw,'2004-10-15')?

select?本年第多少周=datename(week,'2004-10-15')
??????,今天是周几=datename(weekday,'2004-10-15')

7.用一条语句得出某日期所在月份的最大天数?
SELECT DAY(DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd, 1-DAY(‘2004-8-31‘), ‘2004-8-31‘)))) AS ‘Day Number‘