datetime Comparision
-
Hello, I have some problem with my Simple query. I just want to compare the DateTime value to a given range. Here i am giving my query and result. In Result, I do not understand why is selects 2 and 3 numbered rows. Any Other way to Compare datetime value to Date And Time of the field of a table?? SELECT * FROM dbo.Table WHERE ((CAST('5/6/2006 5:08:00 AM' AS DATETIME) BETWEEN StartDateTime AND EndDateTime) OR CAST('5/6/2006 5:08:59 AM' AS DATETIME) BETWEEN StartDateTime AND EndDateTime) RESULT :- 1 2006-05-06 05:04:00.000 2006-05-08 05:05:00.000 2 2006-05-06 05:07:00.000 2006-05-08 05:08:00.000 3 2006-05-06 05:07:00.000 2006-05-08 05:08:00.000 Thank you
-
Hello, I have some problem with my Simple query. I just want to compare the DateTime value to a given range. Here i am giving my query and result. In Result, I do not understand why is selects 2 and 3 numbered rows. Any Other way to Compare datetime value to Date And Time of the field of a table?? SELECT * FROM dbo.Table WHERE ((CAST('5/6/2006 5:08:00 AM' AS DATETIME) BETWEEN StartDateTime AND EndDateTime) OR CAST('5/6/2006 5:08:59 AM' AS DATETIME) BETWEEN StartDateTime AND EndDateTime) RESULT :- 1 2006-05-06 05:04:00.000 2006-05-08 05:05:00.000 2 2006-05-06 05:07:00.000 2006-05-08 05:08:00.000 3 2006-05-06 05:07:00.000 2006-05-08 05:08:00.000 Thank you
Are you sure you posted this correctly? The first datetime in the query is satisfied by the values in 2 & 3, but neither value is saisfied by row 1 in the return. Note that BETWEEN is inclusive: BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression. if you want to exclude the start and end values, use
SELECT *
FROM dbo.Table
WHERE ((CAST('5/6/2006 5:08:00 AM' AS DATETIME) > StartDateTime)
AND (CAST('5/6/2006 5:08:00 AM' AS DATETIME) < EndDateTime))We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene
-
Are you sure you posted this correctly? The first datetime in the query is satisfied by the values in 2 & 3, but neither value is saisfied by row 1 in the return. Note that BETWEEN is inclusive: BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression. if you want to exclude the start and end values, use
SELECT *
FROM dbo.Table
WHERE ((CAST('5/6/2006 5:08:00 AM' AS DATETIME) > StartDateTime)
AND (CAST('5/6/2006 5:08:00 AM' AS DATETIME) < EndDateTime))We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene
Hi, Thanks for the Reply. I have tried everyway. But Still have not been successfull. SELECT * FROM dbo.Table WHERE (((CAST('5/7/2006 1:10:00 AM' AS DATETIME)) > dbo.LoadTestscheduler.StartDateTime AND (CAST('5/7/2006 1:10:00 AM' AS DATETIME)) < dbo.LoadTestscheduler.KillDateTime)) Result:- 0 0 2006-05-07 01:03:00.000 2006-05-09 01:04:00.000
-
Hi, Thanks for the Reply. I have tried everyway. But Still have not been successfull. SELECT * FROM dbo.Table WHERE (((CAST('5/7/2006 1:10:00 AM' AS DATETIME)) > dbo.LoadTestscheduler.StartDateTime AND (CAST('5/7/2006 1:10:00 AM' AS DATETIME)) < dbo.LoadTestscheduler.KillDateTime)) Result:- 0 0 2006-05-07 01:03:00.000 2006-05-09 01:04:00.000
I don't see your problem: '5/7/2006 1:10:00 AM' is later than (>) 2006-05-07 01:03:00.000 AND '5/7/2006 1:10:00 AM' is earlier than (<) 2006-05-09 01:04:00.000 Are you trying to compare just the time values, irrespective of the date? We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene
-
I don't see your problem: '5/7/2006 1:10:00 AM' is later than (>) 2006-05-07 01:03:00.000 AND '5/7/2006 1:10:00 AM' is earlier than (<) 2006-05-09 01:04:00.000 Are you trying to compare just the time values, irrespective of the date? We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene
Thanks for Quick Reply. My Query is Long and involves join of two table. But, I found this perticular area where problem lies. I do not understand why it is not comparing with time. I want it to compare with date as well as time. From Some of the Web site I found that I can compare Binary Values datetime field. But, when i do so, I am getting Arithmatic Overflow error. The link below has information for converting into binary. But, I does not work for me. http://www.sql-server-performance.com/fk\_datetime.asp making even AM to PM or visa versa also does not work. SELECT * FROM dbo.LoadTestscheduler WHERE '2006/05/07 1:10:00 PM' > dbo.LoadTestscheduler.StartDateTime AND '2006/05/07 1:10:00 PM' < dbo.LoadTestscheduler.KillDateTime Can you tell me How can i convert my date to a perticular format?? Do I have to use CONVERT With/Without or In addition to CAST?? Thank you.
-
Thanks for Quick Reply. My Query is Long and involves join of two table. But, I found this perticular area where problem lies. I do not understand why it is not comparing with time. I want it to compare with date as well as time. From Some of the Web site I found that I can compare Binary Values datetime field. But, when i do so, I am getting Arithmatic Overflow error. The link below has information for converting into binary. But, I does not work for me. http://www.sql-server-performance.com/fk\_datetime.asp making even AM to PM or visa versa also does not work. SELECT * FROM dbo.LoadTestscheduler WHERE '2006/05/07 1:10:00 PM' > dbo.LoadTestscheduler.StartDateTime AND '2006/05/07 1:10:00 PM' < dbo.LoadTestscheduler.KillDateTime Can you tell me How can i convert my date to a perticular format?? Do I have to use CONVERT With/Without or In addition to CAST?? Thank you.
DotNetDominator wrote:
My Query is Long and involves join of two table. But, I found this perticular area where problem lies. I do not understand why it is not comparing with time. I want it to compare with date as well as time.
From the examples you provided, it does appear to be working correctly, for the full datetime value (note the bolded date values in my previous post, the date difference makes the answer valid for the given query, since the supplied date-time falls between the two date-times in the returned row.) Why do you think it is not working? I don't seem to understand what you are trying to accomplish. Perhaps some more examples (both good and bad) would clear this up for me. We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene
-
DotNetDominator wrote:
My Query is Long and involves join of two table. But, I found this perticular area where problem lies. I do not understand why it is not comparing with time. I want it to compare with date as well as time.
From the examples you provided, it does appear to be working correctly, for the full datetime value (note the bolded date values in my previous post, the date difference makes the answer valid for the given query, since the supplied date-time falls between the two date-times in the returned row.) Why do you think it is not working? I don't seem to understand what you are trying to accomplish. Perhaps some more examples (both good and bad) would clear this up for me. We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene
Hi, My Problem is solved. Credit goes to you. I was not really looking in to Day when i compared my value with start and end date. There was some error in my Logic. I am really sorry for giving unnecessary trouble. I am really surprised, How can i make such foolish thing.. Thank you very for your help.
-
Hi, My Problem is solved. Credit goes to you. I was not really looking in to Day when i compared my value with start and end date. There was some error in my Logic. I am really sorry for giving unnecessary trouble. I am really surprised, How can i make such foolish thing.. Thank you very for your help.
No problem. It's easy to overlook simple things in one's own code (I do it more often than I'd care to admit). That is one of the great values that comes from having others review your code (particularly when having a problem) - they will often see things you are temporarily blind to. We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest. Patricia Aburdene