<pet peeve>
Ah - dates... :->
You are sorting your data as strings, which in this case gives you:
1/1/1000
12/12/2000
12/31/9999
8/07/2002
8/19/2002
Make sure:
1: Store dates only in DateTime columns in your database.
2: Keep date variables as "native" date format in your apps.
3: Perform explicit conversions when interfacing between subsystems using
text-based interfaces (i.e. User Input/Output, SQL Statements):
a: Use a "date/calendar control" to obtain date input from a user
and to provide date output.
b: Use a "safe" conversion to date variable (in VBScript a Date
subtype of a variant). The safest conversion is based on DateSerial() and
TimeSerial().
c: Use stored procedures with typed parameters to interface with
the database, or alternatively format your date variable in an explicit
string format when building SQL statements. Use a string format with a
non-ambiguous Style defined in SQL Server. My personal favorites styles are
120 and 112.
d: Use the SQL Server CONVERT(DateTime, <Your explicitly
Pre-Formatted date/time string>, <Style>) in your SQL statement in order to
force SQL Server to interpret the value as a date and not a string.
e: Leave datetime column output as dates in result sets. This will
result in a Date (sub-)type variable for that column.
Do your sorting in the SQL statement if possible.
If you have a Grid with output from a query and then use sorting methods of
that grid, beware that it uses string sort on the displayed text, and not a
native sort on the underlying column/variable type. This will also give
sorting problems with numbers (1, 10, 2, ...) In this case, you can get
proper sorting by using hidden columns where the DateTime or number is
displayed (but not shown to the user) in a format that WOULD provide a
proper sort - i.e. with a canonical date format (YYYYMMDDHHNNSS - delimiters
optional) and fixed length numeric strings with leading zeros.
</pet peeve>
HTH,
Tore.
-----Original Message-----
From: Dave Green [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 21, 2002 5:47 PM
To: ActiveServerPages
Subject: Sorting SQL Server Dates..
What's the scoop with sorting SQL Server dates?
Some sample data:
8/19/2002
8/07/2002
1/1/1000
12/12/2000
12/31/9999
Running a query against this data like:
Select theDate from theTable order by theDate
Returns an invalid sort order. Thanks for your help.
--
Dave Green | PremierStudios | 913.438.1004
-----Original Message-----
From: Ben Timby [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 21, 2002 6:38 PM
To: ActiveServerPages
Subject: RE: website statistics
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I would not suggest an ASP app for WWW stats. You should get a log
parser. It will filter your HTTP logs, and give you stats. AnaLOG is a
good/free one.
Ben Timby
Webexcellence
PH: 317.423.3548 x23
TF: 800.808.6332 x23
FX: 317.423.8735
[EMAIL PROTECTED]
www.webexc.com
- -----Original Message-----
From: Brett M. Spahr [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 21, 2002 1:10 PM
To: ActiveServerPages
Subject: website statistics
Does anyone know of a good, free asp app for tracking
website statistics?
- ---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To
unsubscribe send a blank email to %%email.unsub%%
-----BEGIN PGP SIGNATURE-----
Version: PGP 7.0.4
iQA/AwUBPWQdaPnby1cCm2Q8EQJKPgCgyITj7o7i/fbH/hkjs/ck7GGKz4YAoIrA
o9YZThzhrVV6g9laZxj6iwFg
=d+J3
-----END PGP SIGNATURE-----
---
You are currently subscribed to activeserverpages as:
[EMAIL PROTECTED] To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
