Solved

"date" problem

Posted on 1998-08-31
13
148 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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
Comment Utility
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
Comment Utility
I think I typed everything correctly, but now I spot one typo

.Date/Time>/B>

should say

.Date/Time</B>

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 75

Expert Comment

by:Michel Plungjan
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 100 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial will discuss fancy secure registration forms, with AJAX technology support. In this article I assume you already know HTML and some JS. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you mig…
In this tutorial I will show you how to provide a dynamic RTF document on your website generated with data from your database. For this tutorial you will need Microsoft Word or WordPad, WhizBase and Microsoft Access. In this tutorial I will show …
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now