sql查询的日期格式问题 [英] date formatting issue for sql query

问题描述

你好



我有一个字符串currentDatefrom和currentDateTo，它捕获asp.net中的文本框。

date以ddmmyyyy格式输入。



sql 2005 db将currentDAte列作为dd-mm-yyyy格式的varchar（10）。



我想把它传递给sql构建查询，例如

select * from tblcustomers where currentDAte @currentDATefrom和@currentDATeto。



为了让sql返回正确的值...我需要将字符串currentDATefrom和currentDAteto转换为dd-mm-yyyy格式的datetime，然后将其重新转换回字符串并传入。



任何人都可以指向任何可以帮助我进行从字符串到日期时间转换的链接...我尝试了其中几种并且似乎无法正常工作。

Hello

I have a string currentDatefrom and currentDateTo which captures the textbox in asp.net.
date is entered in ddmmyyyy format.

sql 2005 db has currentDAte column as varchar(10) in the dd-mm-yyyy format.

I would like to pass this to sql to build query like
select * from tblcustomers where currentDAte between @currentDATefrom and @currentDATeto.

In order for sql to return correct values ...I will need to convert strings currentDATefrom and currentDAteto to datetime in the dd-mm-yyyy format and reconvert it back to string and pass that in.

can anyone point me to any link that can help me do the conversion from string to datetime...I tried several of these and doesnt seem to work.

推荐答案

您好b $ b请尝试这个

Hi Please try this one

select * from tblcustomers where  convert(datetime,currentDAte,105) 
between convert(datetime,STUFF(STUFF(@currentDATefrom,3,0,'-'),6,0,'-'),105)  
and convert(datetime,STUFF(STUFF(@currentDATeto,3,0,'-'),6,0,'-'),105)  

这不是一个更好的解决方案，请根据上述评论更改数据库。

This is not a preferable solution please make your database change as per above comments.

As西尼萨指出，你的设计很糟糕。您不应将日期存储为数据库中的字符串。但是请看 CONVERT [ ^ ]功能。从所有格式105适合您：

As Sinisa pointed out, your design is bad. You should not store dates as strings in your database. However have a look at CONVERT[^] function. From all the formats 105 is the right one for you:

CONVERT(DATE, '30-11-2014', 105)

假设您的参数是日期而不是字符串，查询应如下所示：

Assuming your parameters are dates and not strings the query should look like this:

select * from tblcustomers where CONVERT(DATE, currentDAte, 105) between @currentDATefrom and @currentDATeto

请注意，这将阻止查询引擎使用currentDate列上的任何索引，因此请保留注重表现。您应该考虑更改数据库。它会让你变得更容易。

Please note that this will prevent query engine to use any indexes on currentDate column so keep an eye on performance. You should really consider changing your database. It will make your like much easier.

试试这个。

Try this.

select * 
from tblcustomers 
where Convert(Date,currentDAte,103) 
       between Convert(Date,@currentDATefrom,103)
             and Convert(Date,@currentDATeto,103)

您可以查看 CAST和CONVERT [ ^ ]



有助于co的SQL Server函数隐藏日期和时间...... [ ^ ]

这篇关于sql查询的日期格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！