求sql日期拼接方法?

[13abyKnight]

表A
id (int) year(int) month (int) day (int) hours (int) Minutes(int) Seconds(float)
1 2009 12 26 23 13 4.0

合并前结果:
year month day hours minutes seconds
2009 12 26 23 13 4.0
合并后结果:
2009-12-26 23:13:4.0


欢迎拍砖

[13abyKnight]

无聊自己顶下

[navy887]

select year+'-'+ month+'-'+  day+' '+ hours+':'+minutes+':'+seconds from tb

[--小F--]

select cast(
cast(year as varchar)
+cast(month as varchar)
+cast(day as varchar)
+cast(hours as varchar)
+cast(minutes as varchar)
+cast(seconds as varchar) as datetime)

[jiangshun]

select left(year,4+'-'+left(month,2)+'-'+left(day,2)+' '+left(hours,2)+':'+left(minutes,2)+''+left(seconds ,2) from 表

[--小F--]

select cast(
cast(year as varchar)
+'-'+cast(month as varchar)
+'-'cast(day as varchar)
+cast(hours as varchar)
+cast(minutes as varchar)
+cast(seconds as varchar) as datetime)

[--小F--]

select cast(
cast(year as varchar)
+'-'+cast(month as varchar)
+'-'cast(day as varchar)
+cast(hours as varchar)
+':'+cast(minutes as varchar)
+':'+cast(seconds as varchar) as datetime)

[navy887]

select cast(year as varchar)+'-'+ cast(month as varchar)+'-'+  cast(day as varchar)+' '+ cast(hours as varchar)+':'+cast(minutes as varchar)+':'+cast(seconds as varchar) from tb

[7761098]

[Quote=引用 6 楼 fredrickhu 的回复:]
SQL codeselectcast(cast(yearasvarchar)+'-'+cast(monthasvarchar)+'-'cast(dayasvarchar)+cast(hoursasvarchar)+':'+cast(minutesasvarchar)+':'+cast(secondsasvarchar)asdatetime)
[/Quote]

这个最标准的，不过我还在想有没有专门的转换时间的函数呢

[华夏小卒]

declare @t table(year int,  month int, day int,hours int, minutes int, seconds int)
insert @t select
2009  ,  12,    26 , 23 ,13, 4.0 


select cast(ltrim(year)+'-'+ltrim(month)+'-'+ltrim(day)+' '
		+ltrim(hours)+':'+ltrim(minutes)+':'+ltrim(seconds) as datetime)
from @t

-----------------------
2009-12-26 23:13:04.000

(1 行受影响)

[sgtzzc]

select 
  id,
  ltrim([year])+'-'+ltrim([month])+'-'+ltrim([day])+' ' --年月日
 +ltrim(hours)+':'+ltrim(Minutes)+':'+ltrim(cast([Seconds] as dec(18,0))+' ' --时分秒
 +ltrim([Seconds]*1000%1000)  --毫秒
from
  A

[13abyKnight]

谢谢。。。。

[sgtzzc]

[Quote=引用 10 楼 sgtzzc 的回复:]
SQL codeselect
id,ltrim([year])+'-'+ltrim([month])+'-'+ltrim([day])+''--年月日+ltrim(hours)+':'+ltrim(Minutes)+':'+ltrim(cast([Seconds]asdec(18,0))+''--时分秒+ltrim([Seconds]*1000%1000)--毫秒from
A
[/Quote]
完善一下

declare @t table(id int,year int,  month int, day int,hours int, minutes int, seconds float)
insert @t select 1,2009,12,26,23,13,4.5 

select 
  id,
  ltrim([year])+'-'+right('00'+ltrim([month]),2)+'-'+right('00'+ltrim([day]),2)+' ' --年月日
 +right('00'+ltrim(hours),2)+':'+right('00'+ltrim(Minutes),2)+':'+right('00'+ltrim(cast([Seconds] as dec(18,0))),2)+' ' --时分秒
 +right('000'+ltrim(cast([Seconds]*1000 as dec(18,0))%1000),3)  --毫秒
 as [date]
from
 @t

/**
id          date
----------- --------------------------------------------
1           2009-12-26 23:13:05 500

(1 行受影响)
**/