字符串转日期语句【百家了乐八大技巧】

 百家乐-数据     |      2020-04-29 18:40

sql server粤语版的私下认可的日期字段datetime格式是yyyy-mm-dd thh:mm:ss.mmm举例:select getdate(卡塔尔二〇〇〇-09-12 11:06:08.177规整了一晃sql server里面恐怕时时会用到的日期格式调换方法:举个例子如下:select convert(varchar, getdate(卡塔尔(قطر‎, 120 卡塔尔二零零二-09-12 11:06:08select convert(varchar(100卡塔尔, getdate(State of Qatar, 0卡塔尔: 05 16 二〇〇七 10:57amselect convert(varchar(100卡塔尔, getdate(卡塔尔国, 1卡塔尔: 05/16/06select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔, 2卡塔尔(قطر‎: 06.05.16select convert(varchar(100卡塔尔, getdate(卡塔尔(قطر‎, 3卡塔尔国: 16/05/06select convert(varchar(100卡塔尔国, getdate(卡塔尔, 4卡塔尔(قطر‎: 16.05.06select convert(varchar(100卡塔尔国, getdate(卡塔尔(قطر‎, 5卡塔尔: 16-05-06select convert(varchar(100卡塔尔, getdate(State of Qatar, 6State of Qatar: 16 05 06select convert(varchar(100卡塔尔, getdate(卡塔尔国, 7卡塔尔: 05 16, 06select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔, 8State of Qatar: 10:57:46select convert(varchar(100卡塔尔, getdate(卡塔尔(قطر‎, 9卡塔尔: 05 16 2007 10:57:46:827amselect convert(varchar(100卡塔尔, getdate(卡塔尔(قطر‎, 10State of Qatar: 05-16-06select convert(varchar(100卡塔尔国, getdate(State of Qatar, 11卡塔尔(قطر‎: 06/05/16select convert(varchar(100卡塔尔国, getdate(State of Qatar, 12卡塔尔: 060516select convert(varchar(100卡塔尔, getdate(卡塔尔国, 13卡塔尔(قطر‎: 16 05 二零零六 10:57:46:937select convert(varchar(100卡塔尔, getdate(State of Qatar, 14卡塔尔国: 10:57:46:967select convert(varchar(100State of Qatar, getdate(State of Qatar, 20卡塔尔(قطر‎: 二〇〇五-05-16 10:57:47select convert(varchar(100State of Qatar, getdate(卡塔尔国, 21State of Qatar: 2006-05-16 10:57:47.157select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔(قطر‎, 22卡塔尔国: 05/16/06 10:57:47 amselect convert(varchar(100卡塔尔国, getdate(卡塔尔国, 23卡塔尔: 2007-05-16select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔国, 24State of Qatar: 10:57:47select convert(varchar(100State of Qatar, getdate(卡塔尔(قطر‎, 25卡塔尔(قطر‎: 二〇〇七-05-16 10:57:47.250select convert(varchar(100卡塔尔, getdate(卡塔尔, 100卡塔尔: 05 16 二零零六 10:57amselect convert(varchar(100卡塔尔国, getdate(卡塔尔国, 101卡塔尔(قطر‎: 05/16/二零零五select convert(varchar(100卡塔尔, getdate(卡塔尔国, 102卡塔尔国: 二零零七.05.16select convert(varchar(100卡塔尔(قطر‎, getdate(State of Qatar, 103State of Qatar: 16/05/二零零七select convert(varchar(100State of Qatar, getdate(卡塔尔(قطر‎, 104卡塔尔: 16.05.二零零七select convert(varchar(100卡塔尔, getdate(State of Qatar, 105卡塔尔: 16-05-二〇〇六select convert(varchar(100卡塔尔, getdate(卡塔尔国, 106卡塔尔国: 16 05 2007select convert(varchar(100卡塔尔, getdate(卡塔尔(قطر‎, 107卡塔尔(قطر‎: 05 16, 二〇〇五select convert(varchar(100卡塔尔, getdate(卡塔尔国, 108State of Qatar: 10:57:49select convert(varchar(100State of Qatar, getdate(卡塔尔, 109State of Qatar: 05 16 二零零五 10:57:49:437amselect convert(varchar(100卡塔尔, getdate(卡塔尔国, 110卡塔尔国: 05-16-二零零七select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔国, 111卡塔尔(قطر‎: 2005/05/16select convert(varchar(100卡塔尔, getdate(卡塔尔(قطر‎, 112State of Qatar: 二零零七0516select convert(varchar(100State of Qatar, getdate(卡塔尔, 113卡塔尔(قطر‎: 16 05 2007 10:57:49:513select convert(varchar(100卡塔尔(قطر‎, getdate(State of Qatar, 114卡塔尔: 10:57:49:547select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔, 120卡塔尔(قطر‎: 二〇〇六-05-16 10:57:49select convert(varchar(100卡塔尔国, getdate(State of Qatar, 121卡塔尔(قطر‎: 二〇〇六-05-16 10:57:49.700select convert(varchar(100卡塔尔(قطر‎, getdate(卡塔尔, 126State of Qatar: 二零零六-05-16t10:57:49.827select convert(varchar(100State of Qatar, getdate(State of Qatar, 130卡塔尔(قطر‎: 18 ???? ?????? 1427 10:57:49:907amselect convert(varchar(100State of Qatar, getdate(卡塔尔, 131卡塔尔(قطر‎: 18/04/1427 10:57:49:920am

方法二

在c#中用惯了datetime.tostring(yyyy-mm-dd卡塔尔国,datetime.tostring(mm/dd/yyyyState of Qatar这种日子与字符串的改造方式,在sql server中没得用,于是乎写了个function,功效跟.net 中的datetime.tostring(formatprovide卡塔尔国方法多数,可是只兑现了日期部分,有意思味的心上人能够把日子部分补充出来。

create function fn_datetostring(@date datetime, @format varchar(20))

returns varchar(20)

as

begin

declare @result varchar(20)

select @result = (replace(replace(replace(@format,'yyyy','20'+substring(convert(char(8),@date,3),7,2)),'mm',substring(convert(char(8),@date,3),4,2)),'dd',substring(convert(char(8),@date,3),1,2)))

return @result

end

使用:

select dbo.fn_datetostring(getdate(),'yyyy-mm-dd')