Solved

"date" problem

Posted on 1998-08-31
13
154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
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 100 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo‚Ķ
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

690 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