我被迫使用存储为字符串的日期值。
例如
Fieldname: claimsDate
data Type: nvarchar
保存格式为mmddyy
。
E.G; 022413
.
我已经使用以下查询将其转换为02/24/2013。
select (left(claimsdate,2) + '/' + substring(claimsdate,3,2) + '/' + case when cast(right(claimsdate,2) as int) >= 70 then '19' else '20' end + right(claimsdate,2)) claimsdate
from tblTable
现在的问题是,我们不能使用该代码执行日期范围检查。
我试过这样使用它:
select (left(claimsdate,2) + '/' + substring(claimsdate,3,2) + '/' + case when cast(right(claimsdate,2) as int) >= 70 then '19' else '20' end + right(claimsdate,2)) claimsdate
from tblTable
where (left(claimsdate,2) + '/' + substring(claimsdate,3,2) + '/' + case when cast(right(claimsdate,2) as int) >= 70 then '19' else '20' end + right(claimsdate,2))
between '03/01/2007' and '12/31/2007'
这并没有给我我正在寻找的范围。它只是随意显示日期。
是否有可能操纵用于日期范围检查的代码?
Thanks alot advance
使用sql中的日期时间转换。这个链接展示了完整的示例http://www.sqlusa.com/bestpractices/datetimeconversion/
select (left(claimsdate,2) + '/' + substring(claimsdate,3,2) + '/' + case when cast(right(claimsdate,2) as int) >= 70 then '19' else '20' end + right(claimsdate,2)) claimsdate
from tblTable
where cast(((case when cast(right(claimsdate,2) as int) >= 70 then '19' else '20' end + right(claimsdate,2) + '-' + left(claimsdate,2) + '-' + substring(claimsdate,3,2))) as datetime)
between @fromDate and @endDate
与@fromDate和@endDate是datetime变量
我希望这将满足您的需求