select id ,name createtime from table1 结果如下
1 aaa 2009-08-27 15:42:00.920
2 bbb 2009-08-27 16:42:00.920
3 ccc 2009-08-25 15:42:00.920
4 ddd 2009-08-23 15:42:00.920
5 eee 2009-08-22 15:42:00.920
求一条sql语句
要求查出来的结果 判断时间为当天的信息只显示 时分 部分 例如:15:42
非当天的信息显示 年月日 例如:2009-08-27最终结果 1 aaa 15:42
2 bbb 16:42
3 ccc 2009-08-25
4 ddd 2009-08-23
5 eee 2009-08-22
1 aaa 2009-08-27 15:42:00.920
2 bbb 2009-08-27 16:42:00.920
3 ccc 2009-08-25 15:42:00.920
4 ddd 2009-08-23 15:42:00.920
5 eee 2009-08-22 15:42:00.920
求一条sql语句
要求查出来的结果 判断时间为当天的信息只显示 时分 部分 例如:15:42
非当天的信息显示 年月日 例如:2009-08-27最终结果 1 aaa 15:42
2 bbb 16:42
3 ccc 2009-08-25
4 ddd 2009-08-23
5 eee 2009-08-22
createtime =case when datediff(DAY,createtime,GETDATE())=0
then SUBSTRING(convert(varchar(19),createtime,120),12,5)
else convert(varchar(10),createtime,120) end
from table1
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int,name varchar(10),createtime datetime)
go
insert tb SELECT
1 , 'aaa', '2009-08-27 15:42:00.920' UNION ALL SELECT
2 , 'bbb', '2009-08-27 16:42:00.920' UNION ALL SELECT
3 , 'ccc', '2009-08-25 15:42:00.920' UNION ALL SELECT
4 , 'ddd', '2009-08-23 15:42:00.920' UNION ALL SELECT
5 , 'eee', '2009-08-22 15:42:00.920'
goselect id ,name ,
createtime =case when datediff(DAY,createtime,GETDATE())=0
then SUBSTRING(convert(varchar(19),createtime,120),12,5)
else convert(varchar(10),createtime,120) end
from tb
go
/*
id name createtime
----------- ---------- ----------
1 aaa 15:42
2 bbb 16:42
3 ccc 2009-08-25
4 ddd 2009-08-23
5 eee 2009-08-22
*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int,name varchar(10),createtime datetime)
go
insert tb SELECT
1 , 'aaa', '2009-08-27 15:42:00.920' UNION ALL SELECT
2 , 'bbb', '2009-08-27 16:42:00.920' UNION ALL SELECT
3 , 'ccc', '2009-08-25 15:42:00.920' UNION ALL SELECT
4 , 'ddd', '2009-08-23 15:42:00.920' UNION ALL SELECT
5 , 'eee', '2009-08-22 15:42:00.920'
goselect id ,name ,
createtime =case when datediff(DAY,createtime,GETDATE())=0
then datename(hour,createtime)+':'+datename(MINUTE,createtime)
else convert(varchar(10),createtime,120) end
from tb
go
/*
id name createtime
----------- ---------- ----------
1 aaa 15:42
2 bbb 16:42
3 ccc 2009-08-25
4 ddd 2009-08-23
5 eee 2009-08-22
*/
这样也可以
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[name] varchar(3),[createtime] datetime)
insert [tb]
select 1,'aaa','2009-08-27 15:42:00.920' union all
select 2,'bbb','2009-08-27 16:42:00.920' union all
select 3,'ccc','2009-08-25 15:42:00.920' union all
select 4,'ddd','2009-08-23 15:42:00.920' union all
select 5,'eee','2009-08-22 15:42:00.920'
select convert(varchar(5),[createtime],108) from TBselect id,name,
[createtime]=case when datediff(dd,[createtime],getdate())=0 then datename(hh,[createtime])+':'+datename(mi,[createtime])
else convert(varchar(20),[createtime],120) end from TB/*
id name createtime
----------- ---- -------------------------------------------------------------
1 aaa 15:42
2 bbb 16:42
3 ccc 2009-08-25 15:42:00
4 ddd 2009-08-23 15:42:00
5 eee 2009-08-22 15:42:00(5 行受影响)*/
drop table TB
left(replace(convert(varchar(20),createtime,120),
convert(varchar(10),getdate(),120),''),10)
from tb
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-27 19:21:39
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(3),[createtime] datetime,[from] sql_variant,[table1] sql_variant)
insert [tb]
select 1,'aaa','2009-08-27 15:42:00.920',null,null union all
select 2,'bbb','2009-08-27 16:42:00.920',null,null union all
select 3,'ccc','2009-08-25 15:42:00.920',null,null union all
select 4,'ddd','2009-08-23 15:42:00.920',null,null union all
select 5,'eee','2009-08-22 15:42:00.920',null,null
--------------开始查询--------------------------
select
id ,[name],
createtime =left(replace(convert(varchar(20),createtime,120),convert(varchar(10),getdate(),120),''),10)
from
tb
----------------结果----------------------------
/*id name createtime
----------- ---- --------------------
1 aaa 15:42:00
2 bbb 16:42:00
3 ccc 2009-08-25
4 ddd 2009-08-23
5 eee 2009-08-22(5 行受影响)*/
createtime =case when datediff(DAY,createtime,GETDATE())=0
then SUBSTRING(convert(varchar(19),createtime,120),12,5)
else convert(varchar(10),createtime,120) end
from table1
select id ,name ,
createtime =case when datediff(DAY,createtime,GETDATE())=0
then SUBSTRING(convert(varchar(19),createtime,120),12,5)
else convert(varchar(10),createtime,120) end
from table1
insert @t SELECT
1 , 'aaa', '2009-08-27 15:42:00.920' UNION ALL SELECT
2 , 'bbb', '2009-08-27 16:42:00.920' UNION ALL SELECT
3 , 'ccc', '2009-08-25 15:42:00.920' UNION ALL SELECT
4 , 'ddd', '2009-08-23 15:42:00.920' UNION ALL SELECT
5 , 'eee', '2009-08-22 15:42:00.920' select id
,name
,createtime = case
when exists(select * from @t where datediff(dd,createtime,a.createtime) =0 and id <> a.id)
then SUBSTRING(convert(varchar(19),createtime,120),12,5)
else convert(varchar(10),createtime,120)
endfrom @t aid name createtime
----------- ---------- ----------
1 aaa 15:42
2 bbb 16:42
3 ccc 2009-08-25
4 ddd 2009-08-23
5 eee 2009-08-22(所影响的行数为 5 行)
left(replace(convert(varchar(20),createtime,120),
convert(varchar(10),getdate(),120),''),10)
from tb