?
Solved

"date" problem

Posted on 1998-08-31
13
Medium Priority
?
162 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:sunjian061197
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Michel Plungjan
ID: 1828377
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
 
LVL 28

Expert Comment

by:sybe
ID: 1828378
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
 

Author Comment

by:sunjian061197
ID: 1828379
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
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
LVL 28

Expert Comment

by:sybe
ID: 1828380
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
 
LVL 8

Expert Comment

by:Answers2000
ID: 1828381
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
 
LVL 8

Expert Comment

by:Answers2000
ID: 1828382
I think I typed everything correctly, but now I spot one typo

.Date/Time>/B>

should say

.Date/Time</B>

0
 
LVL 75

Expert Comment

by:Michel Plungjan
ID: 1828383
Answers2000: Won't is sort all the 1st of all months then all second of all months?

Michel
0
 
LVL 75

Expert Comment

by:Michel Plungjan
ID: 1828384
Answers2000: Won't it sort all the 1st of all months then all second of all months?

Michel
0
 
LVL 8

Expert Comment

by:Answers2000
ID: 1828385
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
 
LVL 28

Expert Comment

by:sybe
ID: 1828386
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
 
LVL 8

Expert Comment

by:Answers2000
ID: 1828387
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
 

Author Comment

by:sunjian061197
ID: 1828388
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
 
LVL 28

Accepted Solution

by:
sybe earned 400 total points
ID: 1828389
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will focus on how to use WhizBase as a tool for sending ICQ messages to ICQ. Here I will use a new technology in WhizBase, published in WhizBase 5.1 version. In this tutorial I will use 3 files, pager.wbsp for the processing, e…
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question