"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
sunjian061197Asked:
Who is Participating?
 
sybeConnect With a Mentor Commented:
SQL has a field type called "datetime" which should be used for date/time variables. Sorting on this field will sort on date order.
0
 
Michel PlungjanIT ExpertCommented:
1. You would make life easier for yourself if the date field was yyyymmdd since that is much easier to sort.
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
0
 
sybeCommented:
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sunjian061197Author Commented:
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
0
 
sybeCommented:
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 ?
0
 
Answers2000Commented:
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">Click here</A>

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/Time>/B>
<%begindetail%>
<TR><TD><%fielda%><TD><%fieldb%><TD><%fielddatetime%>
<%enddetail>
</TABLE>
</BODY>
</HTML>

Is this what you want ?
Incidentally you say "my SQL Server" - which SQL Server - Microsoft ?
0
 
Answers2000Commented:
I think I typed everything correctly, but now I spot one typo

.Date/Time>/B>

should say

.Date/Time</B>

0
 
Michel PlungjanIT ExpertCommented:
Answers2000: Won't is sort all the 1st of all months then all second of all months?

Michel
0
 
Michel PlungjanIT ExpertCommented:
Answers2000: Won't it sort all the 1st of all months then all second of all months?

Michel
0
 
Answers2000Commented:
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...

0
 
sybeCommented:
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.

0
 
Answers2000Commented:
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.
0
 
sunjian061197Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.