如何根据日期条件获取记录? [英] How to get records on base of Date condition?
问题描述
我在sql server 2008中有一个名为PersonalInformation的表。在此表中有一个名为CreateDate的列,它存储插入记录的日期。现在我想根据日期从表中搜索记录。我的搜索字符串就像Month-Year,CreateDate是使用Sql Server的GETDATE()函数存储的。如果我使用格式月 - 年搜索例如:2012年3月我希望显示与表记录匹配的月份和年份,并且还显示在2012年3月之前插入的记录。我怎么能实现这个目标?在此先感谢。
尝试使用当前日期在后端进行验证
其中 DATENAME(MONTH,CreateDate)+ ' - ' + DATENAME(YEAR,CreateDate)= DATENAME(MONTH,GETDATE())+ ' - ' + DATENAME(年份,GETDATE())
或者如果你从前端传递值作为字符串使用这个
选择 * 来自 es002300T0 其中 DATENAME(MONTH,CreateDate)+ ' - ' + DATENAME(年份,CreateDate)= @ dn
测试数据
< pre lang =SQL> 声明 @ dn varchar ( 20 )
set @ dn = ' 2014年8月'
祝你好运; - )
只需转换
其中CONVERT(varchar(10),CreateDate,100)= CONVERT(varchar(10),GETDATE(),100)
我的意思是,如果您的要求是匹配2012年3月以匹配只有月份和年份的案例。
如果任何案例的月份和年份匹配,则检索记录
用于测试首先我给出一个只给出年和月的例子
select convert(varchar(7),getdate(),111 )
参见上面的查询将返回你像2014/08的Restuls 在这种情况下,我们将确认两边的相同比赛案例确定
所以我们的查询将如下所示
其中CONVERT(varchar(7),CreateDate,111)= CONVERT(varchar(7),GETDATE(),111)
其中CONVERT(varchar(10),CreateDate,100)= CONVERT(varchar(10),@ searchdate,100)
即请使用存储过程,其中参数为@searchdate并使用上述条件,并从前端使用存储过程并将输入的日期作为参数传递。
I have a table in sql server 2008 name PersonalInformation. in this table there is a column named CreateDate which stores the date for inserted record. Now i want to search record from table according to date. My search string is like Month-Year and CreateDate is store using GETDATE() function of Sql Server. If i search using The format Month-Year for eg: March-2012 i want the month and year which match with table record is displayed and also record inserted before March-2012 also displayed. How could i achieve this? Thanks in advance.
try this if u r validating in back end using current date
where DATENAME(MONTH, CreateDate) + '-' + DATENAME(YEAR, CreateDate)=DATENAME(MONTH, GETDATE()) + '-' + DATENAME(YEAR, GETDATE())
or use this if u r passing value as string from front end
select * from es002300T0 where DATENAME(MONTH, CreateDate) + '-' + DATENAME(YEAR, CreateDate)=@dn
tested data
declare @dn varchar(20) set @dn='August-2014'
good luck ;-)
Simply you can put your condition on date with by converting
where CONVERT(varchar(10),CreateDate,100) = CONVERT(varchar(10),GETDATE(),100)
I mean if your requirement is to match "March-2012" that to match case only month and year.
If month and year of any case match then to retrieve the record
For to test First I am giving one example which will give you only year and month
select convert(varchar(7),getdate(),111)
See in above query will return you Restuls like "2014/08" In this case we will confirm on same match case on both side ok
So our query will be like below
where CONVERT(varchar(7),CreateDate,111) = CONVERT(varchar(7),GETDATE(),111)
where CONVERT(varchar(10),CreateDate,100) = CONVERT(varchar(10),@searchdate,100)
i.e please use a stored procedure for this which takes a parameter as "@searchdate" and use above condition and from your front end use the stored procedure and pass the entered date as parameter.
这篇关于如何根据日期条件获取记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!