SELECT distinct 卡号,convert(varchar(12),时间,111)+ ',' +convert(varchar(12),时间,108)+ ',01'FROM Records
select distinct 卡号，convert(varchar,时间,111)+','+convert(varchar,时间,108)+',01' as 时间 FROM Records
select convert(varchar,getdate(),111)+','+convert(varchar,getdate(),108)+',01' 时间
/*
时间
----------------------------------------------------------------
2009/04/09,20:59:27,01
(1 行受影响)
*/
select convert(varchar(10),getdate(),111)+','+convert(varchar(10),getdate(),108)+',01'
select convert(varchar(10),getdate(),111)+','+convert(varchar(10),getdate(),108)+',01'
------------------------
2009/04/09,21:02:10,01
(1 行受影响)
SELECT distinct 卡号，convert(char(10),时间,111)+','+right(convert(char(19),时间,120),8)+',1' FROM Records
if object_id('[Records]') is not null drop table [Records]
go
create table [Records]([卡号] varchar(8),[时间] datetime)
insert [Records]
select '03031601','2009-03-02 07:47:11.000'
select
卡号,
replace(replace(replace(convert(varchar(30),时间,121),'-','/'),' ',','),'.000',',01') as 时间
from [Records]
--测试结果:
/*
卡号 时间
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
03031601 2009/03/02,07:47:11,01
（所影响的行数为 1 行）
*/
select distinct
卡号+','+convert(varchar,时间,111)+','+convert(varchar,时间,108)+',01' as 日期加时间
FROM Records
/*
日期加时间
-------------------------------------------------------------------------
03031601,2009/03/02,07:47:11,01
(1 行受影响)
*/
SELECT distinct 卡号，convert(varchar(10),时间,111)+','+right(convert(varchar(19),时间,120),8)+ FROM Records
SELECT distinct 卡号+','+convert(varchar(10),时间,111)+','+right(convert(varchar(19),时间,120),8)+ FROM Records