sqlserver时间表,sqlserver当前时间

sqlserver2005，要做个时间表

合适呀，而且应该要用datetime类型，要不然，如果定义成字符型char or varchar,查询时要进行类型转换。如果定义成datetime,只赋值时间之话，则日期自动填充为1900/1/1，如1900/1/1 9:00

判断早，晚，夜的SQL语句：

select case when begintime=cast('9:00:00' as datetime) and endtimecast('14:00:00' as datetime) then '早' when begintime=cast('14:00:00' as datetime) and endtimecast('21:00:00' as datetime) then '晚' else ‘夜' end

from 表

相关问答

Q1: sqlserver 日期表的问题。

DECLARE @y VARCHAR(4),@m VARCHAR(2)

declare @sdate DATETIME

SET @y='2013'

SET @m='11'

set @sdate =@y +'-' +@m + '-01'

SELECT @sdate

select DATEADD(dd,number,@sdate) as date 

from master..spt_values where type = 'P' 

and DATEADD(dd,number,@sdate)  DATEADD(mm,1,@sdate)

结果：

2013-11-01 00:00:00.000

2013-11-02 00:00:00.000

2013-11-03 00:00:00.000

2013-11-04 00:00:00.000

2013-11-05 00:00:00.000

2013-11-06 00:00:00.000

2013-11-07 00:00:00.000

2013-11-08 00:00:00.000

2013-11-09 00:00:00.000

2013-11-10 00:00:00.000

2013-11-11 00:00:00.000

2013-11-12 00:00:00.000

2013-11-13 00:00:00.000

2013-11-14 00:00:00.000

2013-11-15 00:00:00.000

2013-11-16 00:00:00.000

2013-11-17 00:00:00.000

2013-11-18 00:00:00.000

2013-11-19 00:00:00.000

2013-11-20 00:00:00.000

2013-11-21 00:00:00.000

2013-11-22 00:00:00.000

2013-11-23 00:00:00.000

2013-11-24 00:00:00.000

2013-11-25 00:00:00.000

2013-11-26 00:00:00.000

2013-11-27 00:00:00.000

2013-11-28 00:00:00.000

2013-11-29 00:00:00.000

2013-11-30 00:00:00.000

Q2: 如何用SQL语句查询SQLserver一个表中某个时间段内所有整点时间的值

使用datename函数来取时间中分钟的值。因为整点，都是0分钟。

例：select datename(n,getdate()) 整点则返回 0

需要注意取分钟的参数是n,而不是minute的缩写m,因为月份month的缩写也是m。

Q3: sqlserver 查询时间表问题..

create function generateTime

(

@begin_date datetime,

@end_date datetime

)

returns @t table(date datetime)

as

begin

with maco as (

select @begin_date AS date union all

select date+1 from maco where date+1 =@end_date)

insert into @t

select * from maco option(maxrecursion 0);

return end