T-SQL问题。尝试操作dateTime列以仅检索所需的日期范围 [英] T-SQL Question. Trying to manipulate the dateTime column to only retrieve a desired date range
问题描述
在SQL SERVER 2005/2008中,我试图检索一定范围的日期。我试图使用CONVERT将DateTime数据类型转换为数字和小数,但这不起作用。它给了我一个虚构的数字。你将如何操纵DateTime数据类型以使用T-SQL集中于所需的日期范围????
日期和时间函数(Transact-SQL) [ ^ ]
希望有帮助
为什么要转换它?如果您有DateTime,请将其用作DateTime!
SELECT * FROM myTable WHERE dateEntered< ''2011-05-01''
查看日期时间列,U需要转换112格式。请查看以下示例。
DECLARE @ DateTime 表(ID INT IDENTITY ( 1 , 1 ),EmpName VARCHAR ( 100 ),DOJ DATETIME )
DECLARE @ FDate DATETIME , @ TDate DATETIME
INSERT INTO @ DateTime (EmpName,DOJ)
SELECT ' Kumar',GETDATE()
UNION ALL
SELECT ' Raja',GETDATE() - 10
UNION ALL
SELECT ' Sive',GETDATE() - 50
UNION ALL
SELECT ' Venkat',GETDATE() - 100
SELECT @ FDate = GETDATE() - 30, @ TDate = GETDATE()
SELECT @ FDate , @TDate
SELECT * FROM @日期时间跨度>
WHERE CONVERT ( VARCHAR ( 10 ),DOJ, 112 ) BETWEEN CONVERT ( VARCHAR ( 10 ), @ FDate , 112 ) AND CONVERT ( VARCHAR ( 10 ), @ TDate , 112 )
在此示例中,DateTime Column值如2012-01-23 18:32:59.960被转换为20120123(我们将这样做为Number或Varchar)。
这种格式112给你2012年,01月23日(20120123)。
问候,
GVPrabu
In SQL SERVER 2005/2008 I am trying to retrieve a certain range of dates. I tried to convert the DateTime datatype to a numeric and a decimal using CONVERT, but this did not work. It gave me a ficticous number. How would you manipulate the DateTime datatype to focus in on a desired range of dates using T-SQL????
Date and Time Functions (Transact-SQL)[^]
Hope it helps
Why convert it at all? If you have a DateTime, use it as a DateTime!
SELECT * FROM myTable WHERE dateEntered < ''2011-05-01''
Hi,
When ever checking date time column, U need to convert "112" format. check the following example.
DECLARE @DateTime TABLE(ID INT IDENTITY(1,1),EmpName VARCHAR(100),DOJ DATETIME ) DECLARE @FDate DATETIME,@TDate DATETIME INSERT INTO @DateTime(EmpName,DOJ) SELECT 'Kumar',GETDATE() UNION ALL SELECT 'Raja',GETDATE()-10 UNION ALL SELECT 'Sive',GETDATE()-50 UNION ALL SELECT 'Venkat',GETDATE()-100 SELECT @FDate=GETDATE()-30,@TDate=GETDATE() SELECT @FDate,@TDate SELECT * FROM @DateTime WHERE CONVERT(VARCHAR(10),DOJ,112) BETWEEN CONVERT(VARCHAR(10),@FDate,112) AND CONVERT(VARCHAR(10),@TDate,112)
In this example the DateTime Column values like "2012-01-23 18:32:59.960" is Converted to "20120123" (We will do this as Number or Varchar).
this format 112 is gives you as 2012 year , 01 month, 23 day (20120123).
With Regards,
GVPrabu
这篇关于T-SQL问题。尝试操作dateTime列以仅检索所需的日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!