May 19, 2015 at 2:00 am
[SQL Server 2008] Problem with ConvertDate
Hi there, hope in your help.
In SQL server I have the column doTableDate set a Datetime: 2015-01-01 14:13:00.000
In my aspx page I have two DropDownList:
DateStart with this value:
<option value="01/01/2015 00:00:00">01/01/2015</option>
DateEnd with this value
<option value="01/01/2015 00:00:00">01/01/2015</option>
I need extract all rows on doTable when the doTableDate is between 2015-01-01 and 2015-01-01 and I have tried this sql query:
SELECT * FROM
[ XXX ].[ doTable ]
WHERE
doTableDate BETWEEN CONVERT (
datetime,
'01/01/2015 00:00:00',
103
)
AND CONVERT (
datetime,
'01/01/2015 00:00:00',
103
);
The output is empty but in doTable I have 89 rows with doTableDate equal to 2015-01-01... why the output of the query in SQL server is empty?
Can you help me?
Thank you in advance.
May 19, 2015 at 2:42 am
Is this the same problem as you're discussing in the BETWEEN thread? If so, please post there and not in a new thread. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 2:45 am
GilaMonster (5/19/2015)
Is this the same problem as you're discussing in the BETWEEN thread? If so, please post there and not in a new thread. Thanks
Thank you Sir.
Not Sir, is a different problem ... in this case the dates are in DropDownList on a aspx page and I need find these dates in DDL with format d/m/y in SQL server Database with format y-m-d...
May 19, 2015 at 2:49 am
Well the problem has the same form, same cause and same solution as the one you discussed in there.
Your upper and lower bounds of the between are the same. That means the query will only return rows where the toDAte is EXACTLY '2015-01-01 00:00:00.000' The date you have in the table is '2015-01-01 14:13:00.000'
I'm sure you'll agree that '2015-01-01 14:13:00.000' is not between '2015-01-01 00:00:00.000' and '2015-01-01 00:00:00.000'. In fact it's 14 hours and 13 minutes after the upper bound of your between
Go back to the BETWEEN thread and read what everyone wrote about using inequalities and about times that datetime data types have and about how you should be doing this kind of comparison.
Your problem has nothing to do with formatting. Dates are not stored as strings in SQL, so the format of a DATETIME data type is not a concern. The problem is that you're ignoring that all datetime data types have times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 3:08 am
GilaMonster (5/19/2015)
Well the problem has the same form, same cause and same solution as the one you discussed in there.Your upper and lower bounds of the between are the same. That means the query will only return rows where the toDAte is EXACTLY '2015-01-01 00:00:00.000' The date you have in the table is '2015-01-01 14:13:00.000'
I'm sure you'll agree that '2015-01-01 14:13:00.000' is not between '2015-01-01 00:00:00.000' and '2015-01-01 00:00:00.000'. In fact it's 14 hours and 13 minutes after the upper bound of your between
Go back to the BETWEEN thread and read what everyone wrote about using inequalities and about times that datetime data types have and about how you should be doing this kind of comparison.
Your problem has nothing to do with formatting. Dates are not stored as strings in SQL, so the format of a DATETIME data type is not a concern. The problem is that you're ignoring that all datetime data types have times.
Okay Sir, thank you for help.
But I have tried this syntax in sql query and the output is always empty:
AND doTableDate BETWEEN CAST('01/01/2015 00:00:00' AS DATE)
AND CAST('01/01/2015 00:00:00' AS DATE)
....
AND doTableDate >= CAST('01/01/2015 00:00:00' AS DATE)
AND doTableDate < CAST('01/01/2015 00:00:00' AS DATE)
May 19, 2015 at 3:10 am
cms9651 (5/19/2015)
But I have tried this syntax in sql query and the output is always empty:
AND doTableDate BETWEEN CAST('01/01/2014 00:00:00' AS DATE)
AND CAST('01/01/2014 00:00:00' AS DATE)
....
AND doTableDate >= CAST('01/01/2014 00:00:00' AS DATE)
AND doTableDate < CAST('01/01/2014 00:00:00' AS DATE)
Well, yes. I just explained why that is the case. Read my previous reply again, then ask yourself, is '2014-01-01 14:13:00.00' < '2014-01-01 00:00:00'
In fact, there are no dates at all which will qualify for that where clause. It's like asking what integer values are >= 0 AND < 0. The answer is none, because a value cannot be both greater than 0 and less than 0 at the same time
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 3:24 am
GilaMonster (5/19/2015)
cms9651 (5/19/2015)
But I have tried this syntax in sql query and the output is always empty:
AND doTableDate BETWEEN CAST('01/01/2014 00:00:00' AS DATE)
AND CAST('01/01/2014 00:00:00' AS DATE)
....
AND doTableDate >= CAST('01/01/2014 00:00:00' AS DATE)
AND doTableDate < CAST('01/01/2014 00:00:00' AS DATE)
Well, yes. I just explained why that is the case. Read my previous reply again, then ask yourself, is '2014-01-01 14:13:00.00' < '2014-01-01 00:00:00'
In fact, there are no dates at all which will qualify for that where clause. It's like asking what integer values are >= 0 AND < 0. The answer is none, because a value cannot be both greater than 0 and less than 0 at the same time
Sorry Sir, I'm very confused ... :crying:
May 19, 2015 at 3:28 am
GilaMonster (5/19/2015)
Your upper and lower bounds of the between are the same. That means the query will only return rows where the toDAte is EXACTLY '2015-01-01 00:00:00.000' The date you have in the table is '2015-01-01 14:13:00.000'I'm sure you'll agree that '2015-01-01 14:13:00.000' is not between '2015-01-01 00:00:00.000' and '2015-01-01 00:00:00.000'. In fact it's 14 hours and 13 minutes after the upper bound of your between
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 3:30 am
Ok, let's go back to the problem.
What, exactly, are you trying to do? What data do you have? What should be returned by the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 3:38 am
GilaMonster (5/19/2015)
Ok, let's go back to the problem.What, exactly, are you trying to do? What data do you have? What should be returned by the query?
Thank you.
In the doTable in field doTableData I have this rows:
2015-01-01 14:13:00.000
2015-01-01 15:31:00.000
2015-01-01 16:14:00.000
2015-01-01 17:55:00.000
2015-01-01 18:37:00.000
2015-01-01 20:45:00.000
...
total are 89 rows that contain date equal to 2015-01-01
I need extract in output these 89 rows when I have selected in DropDownList DateStart the value:
< option value = " 01/01/2015 00:00:00 " > 01/01/2015 </option>
I am not interested to extract the time, I need only convert the string datetime '01/01/2015 00:00:00' only to date '2015-01-01'.
May 19, 2015 at 3:46 am
Declare @RangeStart varchar(20) = '01/01/2015 00:00:00', @RangeEnd varchar(20) = '01/01/2015 00:00:00' -- these come from the application
WHERE toDate >= CONVERT (datetime,@RangeStart,103) and toDate < DATEADD(dd,1,CONVERT (datetime,@RangeEnd,103))
Even if you don't want to return dates, you have to consider that your dates do have times associated with them and hence you cannot just look at the date values when you're querying and filtering on them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 3:59 am
GilaMonster (5/19/2015)
Declare @RangeStart varchar(20) = '01/01/2015 00:00:00', @RangeEnd varchar(20) = '01/01/2015 00:00:00' -- these come from the applicationWHERE toDate >= CONVERT (datetime,@RangeStart,103) and toDate < DATEADD(dd,1,CONVERT (datetime,@RangeEnd,103))
Even if you don't want to return dates, you have to consider that your dates do have times associated with them and hence you cannot just look at the date values when you're querying and filtering on them
Now working, thank you very much! 🙂
May 19, 2015 at 4:02 am
cms9651 (5/19/2015)
GilaMonster (5/19/2015)
Declare @RangeStart varchar(20) = '01/01/2015 00:00:00', @RangeEnd varchar(20) = '01/01/2015 00:00:00' -- these come from the applicationWHERE toDate >= CONVERT (datetime,@RangeStart,103) and toDate < DATEADD(dd,1,CONVERT (datetime,@RangeEnd,103))
Even if you don't want to return dates, you have to consider that your dates do have times associated with them and hence you cannot just look at the date values when you're querying and filtering on them
Now working, thank you very much! 🙂
Do you understand why it works and why the original one doesn't?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2015 at 4:19 am
GilaMonster (5/19/2015)
cms9651 (5/19/2015)
GilaMonster (5/19/2015)
Declare @RangeStart varchar(20) = '01/01/2015 00:00:00', @RangeEnd varchar(20) = '01/01/2015 00:00:00' -- these come from the applicationWHERE toDate >= CONVERT (datetime,@RangeStart,103) and toDate < DATEADD(dd,1,CONVERT (datetime,@RangeEnd,103))
Even if you don't want to return dates, you have to consider that your dates do have times associated with them and hence you cannot just look at the date values when you're querying and filtering on them
Now working, thank you very much! 🙂
Do you understand why it works and why the original one doesn't?
I think so... because in your code you have added one day to @RangeEnd ... in this mode the query extract all the range selected... I understand it ?
May 19, 2015 at 4:22 am
Pretty much. By adding one day to the end of the range, you're getting all the date-times on that day, right up to just before midnight at the end of the day, whereas the first one, because the start and the end of the range were the same, it would only get dates that have the time portion at midnight (beginning of the day)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply