| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |

01-05-11, 10:28
|
|
Registered User
|
|
Join Date: Aug 2009
Posts: 14
|
|
|
Date difference issue with relational operator
|
|
Date difference issue with relational operator
convert(varchar(25),startDate,105)<convert(varchar (25),getDate() ,105)
if the startDate = '26/11/2010' and getDate = '27/11/2010' - TRUE
if the startDate = '29/11/2010' and getDate = '28/11/2010' - FALSE
wherein with following dates, it returns - TRUE (ideally it should return FALSE)
if the startDate = '01/12/2010' and getDate = '30/11/2010'
Is this a date format issue..? If so, how to fix it ?
__________________
Vikram RM
|
|

01-06-11, 04:59
|
|
Registered User
|
|
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 415
|
|
You are comparing charactervalues, not date/numeric values. < or > gives totally different results when you use charactervalues.
Why do you convert the startdate and enddate to a varchar before you compare them? Why not compare the datetime-values?
__________________
I'm not crazy, I'm an aeroplane!
|
|

01-09-11, 23:28
|
|
Registered User
|
|
Join Date: Jan 2010
Posts: 25
|
|
|
|
even I had faced this issue... n later came to know that the characters values was being compared which actually takes ascii values into consideration, in such case the comparision is totally incorrection.
But I still do not have solution if I just need to consider the date but not the timings. I remember the reason for which I used the convert function was to get rid of the timings and only to consider the date part.
|
|

01-10-11, 03:48
|
|
Registered User
|
|
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 415
|
|
If you only assign date-values to a datetime-variable, the time-part does not matter in the comparison.
If you have to datetimevariables, e.g. '3/1/2010 10:12:00' and '12/9/2011 08:30:03', you can convert the original value to char/varchar in the desired format (without the timepart) en then convert that value back to a datetime value.
Those two converted datetimevalues you can compare properly.
__________________
I'm not crazy, I'm an aeroplane!
|
|

01-10-11, 08:44
|
|
Registered User
|
|
Join Date: May 2005
Location: South Africa
Posts: 1,268
|
|
To truncate time from getdate() use
Code:
where startDate<dateadd(dd, datediff(dd, '', getdate()), '')
|
|

01-18-11, 09:37
|
|
Registered User
|
|
Join Date: May 2010
Location: Hyderabad, India
Posts: 16
|
|
below query is not correct.
dateadd(dd, datediff(dd, '', getdate()), '')
Can any one please correct the same.
Thanks in advance!!
__________________
-PavanKumar M Reddy
|
|

01-19-11, 02:20
|
|
Registered User
|
|
Join Date: May 2005
Location: South Africa
Posts: 1,268
|
|
??
Can you explain what you mean/want?
Code:
1> select dateadd(dd, datediff(dd, '', getdate()), '')
2> go
--------------------------
Jan 19 2011 12:00AM
(1 row affected)
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|