Date sorting based on Varchar datatype
-
Hi
Please help. Need help ASAP.
I have a column with type Varchar and I am populating this column with the date values. This column is displayed in our front end as a list drop down. Since the datatype is Varchar the sorting gets messed up when I open this drop down from our front end. I can not convert this type to datetime because of other system limitations. I need to sort the data in the choice list based on the date.
For example : right now the drop down shows the dates as follows
01/12/2007
01/13/2006
01/26/2007
01/27/2006
But I want these dates to be displayed in Descending order as
01/26/2007
01/12/2007
01/27/2006
01/13/2006.
To achieve this I am thinking adding some hidden character before these dates so that they will be sorted in the proper order. Does any body knows how can I achieve this. Please please help!!!
-
SELECT Col1
FROM Table1
ORDER BY CAST(Col1 AS DATETIME) DESC
N 56°04'39.16"
E 12°55'05.25" -
Thanks Peter, but the query you sent me will work only when i am sorting it in the SQL query. I want my drop down (list) in the front end which is of the type varchar to display these dates in the sorted order and in the format I mentioned above. I hope this is not confusing.
Thanks
Kavita
-
You may disable the sorting property for your combo box; this way your recordset may stay sorted as in Peter's query above.
-
Why not display the dates as 2007/01/27? Is that such a big issue to show the dates in YMD format?
-
I don't think anybody here is using programming language which does not support date/time datatypes.
Use proper datatype, don't create problems for yourself.
It's your boss's job.
_____________
Code for TallyGenerator -
Unless it is a legacy system, and orignal poster wants to create reports in Reporting Services or other reporting applications.
The column is VARCHAR for us unknown reasons.
Sergiy, what about you actually tried to help, instead of just shouting?
Are you sure original poster even can change data type without breaking database rules pk/fk or contraints?
N 56°04'39.16"
E 12°55'05.25" -
Really helpful post.
Really.
Is spamming is the only purpose of your presence here?
_____________
Code for TallyGenerator -
Hi,
The (ageing) DELPHI telecoms project management application I support has drop-down combos which only support the varchar datatype. Luckily I can over-ride the code which fills the combo with my own SQL. I did a bit exercise a while ago to convert all these fields to the datetime datatype and saved myself a whole heap of grief
What programming language is the application written in?
David
If it ain't broke, don't fix it...
-
You mean like telling people to force their boss to solve their problems?
That must be a masterpiece of helpful information. Don't you agree?
It seems to me that you are not used to be criticized. Get used to it if you continue to put people down.
N 56°04'39.16"
E 12°55'05.25" -
Another portion of flame.
Can you offer anything else?
BTW, if could read and understand written text you'd see I did not force anybody's boss to solve their problem.
I said it's boss's job to create problems for them, so they should not do it and leave this activity for the boss.
_____________
Code for TallyGenerator -
Following is the stored procedure I am using to insert dates in the drop down list. I have added comments in the code so that you can understand what I am trying to do here.
create procedure ps_sssstartdate as
begin
declare @thnumber int
declare @ccflxid int
declare @ftcounter int
declare @i_cdcheckdatedesc varchar(20)
/* Cursor to get the set of dates from CALITEM TABLE */
DECLARE cdcheckdate_cursor SCROLL CURSOR for
select distinct
/* following piece of code is added to sort the dates in proper order when inserted in varchar format. I tried to use non printable characters so that they will be added to the date but won't be displayed. When I run this script in SQL QUERY Analyzer it displays dates in order I want them to display but with some wiered characters before the actual date hence in the applicatation from the front end it doesn't display these dates */
case when datepart(yy,cdcheckdate) < datepart(yy,getdate()) then
' ' else '' end +
case when datepart(mm,cdcheckdate) = 12 then
char(10) + char(10)
when datepart(mm,cdcheckdate) = 11 then
char(10) +char(11)
when datepart(mm,cdcheckdate) = 10 then
char(10) +char(12)
when datepart(mm,cdcheckdate) = 9 then
char(10) +char(32)
when datepart(mm,cdcheckdate) = 8 then
char(11) +char(10)
when datepart(mm,cdcheckdate) = 7 then
char(11) +char(11)
when datepart(mm,cdcheckdate) = 6 then
char(11) +char(12)
when datepart(mm,cdcheckdate) = 5 then
char(11) +char(32)
when datepart(mm,cdcheckdate) = 4 then
char(12) +char(10)
when datepart(mm,cdcheckdate) = 3 then
char(12) +char(11)
when datepart(mm,cdcheckdate) = 2 then
char(12) +char(12)
when datepart(mm,cdcheckdate) = 1 then
char(12) +char(32)
end +
case when (convert(varchar,datepart(dd,cdcheckdate)) like '3%' and len(convert(varchar,datepart(dd,cdcheckdate))) = 2) then
char(10)
when (convert(varchar,datepart(dd,cdcheckdate)) like '2%' and len(convert(varchar,datepart(dd,cdcheckdate))) = 2) then
char(11)
when (convert(varchar,datepart(dd,cdcheckdate)) like '1%' and len(convert(varchar,datepart(dd,cdcheckdate))) = 2) then
char(12)
when len(convert(varchar,datepart(dd,cdcheckdate))) = 1 then
char(32)
end +
case when len(convert(varchar,datepart(mm,cdcheckdate))) = 1 then
'0'+convert(varchar,datepart(mm,cdcheckdate))
else
convert(varchar,datepart(mm,cdcheckdate))
end + '/' +
case when len(convert(varchar,datepart(dd,cdcheckdate))) = 1 then
'0'+convert(varchar,datepart(dd,cdcheckdate))
else
convert(varchar,datepart(dd,cdcheckdate))
end + '/' + convert(varchar,datepart(yyyy,cdcheckdate))
from calitem
where datepart(yy,cdcheckdate) >= datepart(yy,getdate())-1 and
cdcheckdate <= dateadd(dd,45,getdate()) and datepart(dw,cdcheckdate) = 6
order by cdcheckdate desc
/* CHBASE below is the table that stores drop down items in the table for drop down list named 'ssstartdate'. This table (CHBASE) has number of other drop down items too. ssstartdate is one of those drop downs. The items that belong to ssstartdate have unique ID called as ccflxid. Get the Unique ccflxid from CHBASE table for this drop down. Items in the drop down are of the type varchar. I can not change the type to datetime since it has other drop down lists too along with this drop down list*/
select @ccflxid = ccflxid from chbase where ccdescrip = 'ssstartdate'
select @ftcounter = ftcounter from flxtable where fttblname = 'Chitem'
delete from chitem where ciflxidcc = @ccflxid
OPEN cdcheckdate_cursor
-- get the first record from the cursor
FETCH cdcheckdate_cursor INTO @i_cdcheckdatedesc
-- while the cursor is not at EOF
WHILE ( @@fetch_status = 0 )
BEGIN
/* To insert items in CHITEM each row has unique key (THNUMBER) that can be get from THING table */
UPDATE Thing Set thnumber = thnumber + 2 where ThFlxID = @ftcounter
select @thnumber = thnumber from thing where ThFlxID = @ftcounter
/* insert first item (date) in the list drop down */
insert into chitem
(cimagic,ciflxidcc,cicrc,cisource,ciflxid,cidateadd,cidatemod,ciusername,cirectype,cidescrip)
values
(863921143,@ccflxid,0,9,@thnumber,getdate(),getdate(),
'SP_sssstartdate','CITM',@i_cdcheckdatedesc)
/* increment unique key for next row to insert in CHITEM */
UPDATE Thing Set thnumber = thnumber + 1 where ThFlxID=@ftcounter
/* Fetch next item */
FETCH cdcheckdate_cursor INTO @i_cdcheckdatedesc
end
CLOSE cdcheckdate_cursor
DEALLOCATE cdcheckdate_cursor
end
GO
Can any one suggest any other way besided non printable characters?
Thanks in advance.
-
We have already suggested several ways. Did you try them and found unsatisfactory? If so, say why, so that we know better what your main problem is.
To sum up, offered solutions are:
- disable sorting property in combo box
- show dates in YYYY/MM/DD format
- override the original code and use datetime datatype (David le Quesne)
-
--I can not disable the sorting option in this combo box because this combo box is stored in the table and this table contains other combo boxes along with this combo box and they need sorting. This sorting option is not available on the table level and due to the system limitations I can not disable sorting option.
-- I did use yyyy/mm/dd format first but client did not want it in this format so I need to find some other way to display the date in mm/dd/yyyy format.
--- If I use datetime datatype it saves the data in the following format 'April 4 2007' where as i want it to display '04/04/07'.
Any other suggestion?
Thanksg
-
No one has any suggestion!!!!!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply