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.

 
Go Back  dBforums > Database Server Software > Sybase > Date difference issue with relational operator

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-05-11, 10:28
vikram3.r@tcs.com vikram3.r@tcs.com is offline
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
Reply With Quote
  #2 (permalink)  
Old 01-06-11, 04:59
Martijnvs Martijnvs is offline
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!
Reply With Quote
  #3 (permalink)  
Old 01-09-11, 23:28
alirulez999 alirulez999 is offline
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.
Reply With Quote
  #4 (permalink)  
Old 01-10-11, 03:48
Martijnvs Martijnvs is offline
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!
Reply With Quote
  #5 (permalink)  
Old 01-10-11, 08:44
pdreyer pdreyer is offline
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()), '')
Reply With Quote
  #6 (permalink)  
Old 01-18-11, 09:37
reddy_546 reddy_546 is offline
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
Reply With Quote
  #7 (permalink)  
Old 01-19-11, 02:20
pdreyer pdreyer is offline
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)
Reply With Quote
Reply

Tags
date, format, issue, operator, relational

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On