我有一个问题,我的表存储日期为
2009-01-10 10:00:00.000
我有另一个表存储日期为
2009-01-10 12:00:00.000
我知道它们不一样但是日期是,在SQL中是否可以轻松地缩短时间并保留比较日期?谢谢.
运行这个
SELECT DATEADD(dd, DATEDIFF(d, 0, Getdate()), 0)
将Getdate()更改为列名以删除日期
顺便说一句,如果你进行比较,最好做> =和<,因为它会表现更好
您可以使用以下语法: -
CAST(date_field AS DATE) as column_name
看这里
你想要第一个.
SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))
不要对varchar使用任何转换,它们很慢.
编辑:@feihtthief - 这是一种从临时数字表中填充日期表(0时间)的方法.如果需要,编辑它以保存数字表.它们也很方便.
DECLARE @DaysFromGoLive int SET @DaysFromGoLive = (SELECT (DATEDIFF(dd,'10/01/2007',GETDATE()) + 1)) /* Days from go live is the starting date of the table */ SELECT TOP 10950 --30 years of days IDENTITY(INT,1,1) as N INTO #Numbers FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 CREATE TABLE [dbo].[TableOfDates]( [fld_date] [datetime] NOT NULL, CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED ( [fld_date] ASC )WITH FILLFACTOR = 99 ON [PRIMARY] ) ON [PRIMARY] INSERT INTO dbo.TableOfDates SELECT DATEADD(dd,nums.n - @DaysFromGoLive,CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date FROM #Numbers nums SELECT MIN(FLD_Date) FROM dbo.TableOfDates SELECT MAX(FLD_Date) FROM dbo.TableOfDates DROP TABLE #Numbers