sunjian061197
asked on
"date" problem
Hi,
I am using Microsoft IIS. I have a SQL database, with each record has a date field. I need to retrieve information from the database and sort the fields ordered by date. I read some materials and they told me that when creating a table with SQL, I can actually create fields that are of the type "Date". I tried that but my SQL Server told me that it could not find the type "Date". I do not know why and I have to use char(10) instead. So is there an easy way to sort the fields using VBScript? My date field's format is dd/mm/yyyy.
Any help is appreciated.
sunjian
I am using Microsoft IIS. I have a SQL database, with each record has a date field. I need to retrieve information from the database and sort the fields ordered by date. I read some materials and they told me that when creating a table with SQL, I can actually create fields that are of the type "Date". I tried that but my SQL Server told me that it could not find the type "Date". I do not know why and I have to use char(10) instead. So is there an easy way to sort the fields using VBScript? My date field's format is dd/mm/yyyy.
Any help is appreciated.
sunjian
SQL has a field type called "datetime" which you could have used.
As concerned sorting the dates once you have them in a string, you could write your own sort function (VBS does not have one), or use server-side javascript, using the sort mentioned by Michel.
I haven't tried it exactly, but it might well be possible to write a datesort function in javascript, which can be called from VBScript:
<script RUNAT="Server" Language="Javascript">
function DateSort(datearray) {
..code
}
</script>
If I get inspired I might do some work for it.
As concerned sorting the dates once you have them in a string, you could write your own sort function (VBS does not have one), or use server-side javascript, using the sort mentioned by Michel.
I haven't tried it exactly, but it might well be possible to write a datesort function in javascript, which can be called from VBScript:
<script RUNAT="Server" Language="Javascript">
function DateSort(datearray) {
..code
}
</script>
If I get inspired I might do some work for it.
ASKER
Thanks for your information,Michel and sybe!
sybe: for the datetime type in SQL, can you tell me what is the actually format if I print it out? i.e. is it something like 31/8/1998 or Aug 31 1998 or something else?
sunjian
sybe: for the datetime type in SQL, can you tell me what is the actually format if I print it out? i.e. is it something like 31/8/1998 or Aug 31 1998 or something else?
sunjian
It might depend on how SQL is installed, I do get a mm/dd/yy, but is a variable of the type "Date", which means that it is easy to turn it into the dateformat you want. I have a couple of (simple) functions in VBS that turn dates into European format (dd/mm/yyyy) or into US-format (mm/dd/yyyy) or use monthnames etc.
What do you want to get out of it ?
What do you want to get out of it ?
Nobody seems to have menitioned that you can do the sort in the query results set, you don't need VB Script or JavaScript.
For example with IDC (Internet Database Connector)
Setup something like:
1. In your main page include a link something like
<A HREF="/scripts/results.idc
2. In results.idc setup something like:
Datasource: Local Server
Username: sa
Template: results.htx
SQL Statement:
+SELECT fielda, fieldb, fielddatetime FROM mytable WHERE fielda = 5 ORDER BY fielddatetime ASC
Change the datasources to match your ODBC parameters, and the query appropriately. I assume your fielddatetime is the name of the field you set as a date/time field. ORDER BY clause sorts the results.
3. In results.htx setup something like
<HTML>
<HEAD><TITLE>some title</TITLE></HEAD>
<TABLE BORDER>
<B><TR><TH>A<TH>B<TH>Date/
<%begindetail%>
<TR><TD><%fielda%><TD><%fi
<%enddetail>
</TABLE>
</BODY>
</HTML>
Is this what you want ?
Incidentally you say "my SQL Server" - which SQL Server - Microsoft ?
For example with IDC (Internet Database Connector)
Setup something like:
1. In your main page include a link something like
<A HREF="/scripts/results.idc
2. In results.idc setup something like:
Datasource: Local Server
Username: sa
Template: results.htx
SQL Statement:
+SELECT fielda, fieldb, fielddatetime FROM mytable WHERE fielda = 5 ORDER BY fielddatetime ASC
Change the datasources to match your ODBC parameters, and the query appropriately. I assume your fielddatetime is the name of the field you set as a date/time field. ORDER BY clause sorts the results.
3. In results.htx setup something like
<HTML>
<HEAD><TITLE>some title</TITLE></HEAD>
<TABLE BORDER>
<B><TR><TH>A<TH>B<TH>Date/
<%begindetail%>
<TR><TD><%fielda%><TD><%fi
<%enddetail>
</TABLE>
</BODY>
</HTML>
Is this what you want ?
Incidentally you say "my SQL Server" - which SQL Server - Microsoft ?
I think I typed everything correctly, but now I spot one typo
.Date/Time>/B>
should say
.Date/Time</B>
.Date/Time>/B>
should say
.Date/Time</B>
Answers2000: Won't is sort all the 1st of all months then all second of all months?
Michel
Michel
Answers2000: Won't it sort all the 1st of all months then all second of all months?
Michel
Michel
Michel - NO - because if you're storing a date/time field you really should use the native SQL type for it (DATE or DATETIME depending on the database), and by doing so will sort correctly.
The biggest reasons to do it the "right way" are
1. MS/Oracle/Sybase/Informix all the other DB vendors have spent more time and $ on date/time functionality than sunijan's ever likely to, even if we all help him.
2. If the results set of the query is large, then sorting in VB script or JavaScript is going to take an age
AND BEST OF ALL 3. It's actually less work to do it the right way.
I'd like to see that sunijan thinks though...
The biggest reasons to do it the "right way" are
1. MS/Oracle/Sybase/Informix all the other DB vendors have spent more time and $ on date/time functionality than sunijan's ever likely to, even if we all help him.
2. If the results set of the query is large, then sorting in VB script or JavaScript is going to take an age
AND BEST OF ALL 3. It's actually less work to do it the right way.
I'd like to see that sunijan thinks though...
Answers,
That was the whole problem, if you read the question: the dates are stored as a string in a string (varchar) field, and not in a datetime field. Of course when the dates are stored in a datetime field, ordering by that field would return the records in date-order.
That was the whole problem, if you read the question: the dates are stored as a string in a string (varchar) field, and not in a datetime field. Of course when the dates are stored in a datetime field, ordering by that field would return the records in date-order.
sybe - yes I did read that, but I think you'll also find that sunijan originally wanted to create the fields with the date type, and only switched to VARCHAR when he couldn't make this work.
Fixing this original problem would give him a better solution (rather than providing a work round on a work round), which is why I asked him which Database he's using.
In any case we really need a response from sunijan.
Fixing this original problem would give him a better solution (rather than providing a work round on a work round), which is why I asked him which Database he's using.
In any case we really need a response from sunijan.
ASKER
Hi,
Very sorry for this late reply... Actually I have to admit that my question is not very clear itself. I should have asked: Is there any type like "date" in SQL database? If not, is there any quick way(existing functions, libraries) of doing the sorting? I think sybe is the first person that gave me the information that there is a type called 'datetime' that can represent the date information. Once this is known, I definitely know I can use the "ORDER BY" clause 'cos after all, that's my very purpose of using the 'datetime' type. I think the credit should go to sybe. Please post it as an answer sybe.
Thanks to all!
sunjian
Very sorry for this late reply... Actually I have to admit that my question is not very clear itself. I should have asked: Is there any type like "date" in SQL database? If not, is there any quick way(existing functions, libraries) of doing the sorting? I think sybe is the first person that gave me the information that there is a type called 'datetime' that can represent the date information. Once this is known, I definitely know I can use the "ORDER BY" clause 'cos after all, that's my very purpose of using the 'datetime' type. I think the credit should go to sybe. Please post it as an answer sybe.
Thanks to all!
sunjian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2. JavaScript 1.1 supports Array.sort() so in MSIE4 and Netscape 3+ you may create JavaScript arrays and sort them:
<SCRIPT LANGUAGE="JavaScript">
myArray = new Array(10);
myArray[0] = '<% date 1 from SQL %>';
myArray[1] = '<% date 2 from SQL %>';
myArray[2] = '<% date 3 from SQL %>';
myArray[3] = '<% date 4 from SQL %>';
myArray[4] = '<% date 5 from SQL %>';
myArray[5] = '<% date 6 from SQL %>';
myArray[6] = '<% date 7 from SQL %>';
myArray[7] = '<% date 8 from SQL %>';
myArray[8] = '<% date 9 from SQL %>';
myArray[9] = '<% date10 from SQL %>';
myArray.sort();
Michel