Supporting alpmoon's answer: Sybase datetime type stores the data in a non-human-readable format. It's up to the client tool and some server-side settings as to how that's displayed. So the WHERE clauses alpmoon has given you do not change the actual data, they're just making it easier for you to make the compare work the way you think it should.
The internal format actually stores dates with times down to the millisecond... therefore two datetimes that aren't the same to the ms aren't actually equal, and your join won't work. Basically they make poor columns to join on. Usually you want to use some kind of range query or join on specific dateparts.
A data warehousing trick is to keep the original datetime but also add extra columns for all the dateparts, so you can join directly on them.
Remember there can be issues with converting dates to character types for ordering and sorting... "10 September 2009" sorts before "2 September 2009" if treated as character data.
Main Topics
Browse All Topics
by: alpmoonPosted on 2009-09-18 at 22:22:52ID: 25371586
It should work. But, if you get the value of one column through getdate or another function providing seconds and other column without seconds, they may not match. In other word, what you see is not the actual format. Internal format includes seconds as well.
What you can do is to convert actual data whatever format you like and then compare:
where convert(char, date1, 101) = convert(char, date2, 101) -- this only compares dates without time
where convert(char, date1, 100) = convert(char, date2, 100) -- this compares date and time combined without seconds
You can check other styles for "convert" at:
http://infocenter.sybase.c