April 13, 2006 at 4:36 pm
How to convert smalldatetime values to mm/dd/yyyy without the time portion and without the unnecessary leading zeros in the mm and dd parts?
Our tables are Microsoft Project server tables where some columns that are defined as smalldatetime so their values are stored as 1/1/2005 12:00:05 AM for example.
The end-users want to know their projects start and end dates so their dates are displayed as 1/1/2005 (date portion only). When they do a Filter on the user interface and attempt to find dates = 1/1/2005 they get a NOT FOUND due to the lack of the time portion.
I can do a convert(char(10), ColX,101) to drop the time portion but the dates now come in a fixed format so 1/1/2005 becomes 01/01/2005.
The end users don't want to have to type 01/01 (ie the unnecessary leading zeros).
Thanks!
April 13, 2006 at 4:41 pm
Will this one work for you?
select convert(varchar(10), getdate(),101)
Minh Vu
April 13, 2006 at 4:45 pm
Thanks, Minh. It does drop the time portion but now the date is in fixed format (ie has the unnecessary leading zeros in the month and day). And a character compare of 01/01/2005 is not equal to 1/1/2005 so the filter/search will still fail.
April 13, 2006 at 5:43 pm
Here is the answer on your question.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=272895&p=1
4 different answers.
Pick the one.
_____________
Code for TallyGenerator
April 13, 2006 at 11:11 pm
I always do what the poster above did but append on another CONVERT(DATETIME... to it.
April 14, 2006 at 5:45 am
It's worst possible way.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply