HELP - Sorting by date, priority, hour problem

HELP - Sorting by date, priority, hour problem

Post by F Rodrigue » Wed, 16 Sep 1998 04:00:00



Hello,

I am new to ASP and SQL server 6.5. I have a few questions.

1) How is a datetime datatype stored in SQL server 6.5? Is it stored as a
string of characters or as an object?

2) Is the stored value dependent on the server language setup (for example
US english server stores in english) or is it independent?

3) We want to sort by date, priority and hour, where the date and hour come
from the same datetime field, and the priority is an integer. Is it better
to do it at the SORT BY clause level or to sort an array of the resulting
recordset using VBScript date functions?

Thanks for any input on this, I would really appreciate any ideas.

Fabian Rodriguez

 
 
 

HELP - Sorting by date, priority, hour problem

Post by Greg Cirin » Wed, 16 Sep 1998 04:00:00


1) SQL Server v 1.1 thru 7 stores the date an time in the format based upon
the language set of the database. in us english sort orders and language
pages it is MMM dd YYYY HH:MM:SS mmm am/pm.  this is a specific datatype but
can be extracted using the convert function or the datepart function which
allows you to utilize a specific component of the datetime result. the
datepart function returns the datepart in the datatype of the component
(e.g. jun will be char by default, 1998 will be int, etc.)

2) SQL Server is functionally based on character set and sort order.  The
functions that return the data do not change functionally

3) your procedure should contain the sort by datefield, priorityfield, and
datepart(hh, datefield)

enjoy
Greg Cirino

Hello,

I am new to ASP and SQL server 6.5. I have a few questions.

1) How is a datetime datatype stored in SQL server 6.5? Is it stored as a
string of characters or as an object?

2) Is the stored value dependent on the server language setup (for example
US english server stores in english) or is it independent?

3) We want to sort by date, priority and hour, where the date and hour come
from the same datetime field, and the priority is an integer. Is it better
to do it at the SORT BY clause level or to sort an array of the resulting
recordset using VBScript date functions?

Thanks for any input on this, I would really appreciate any ideas.

Fabian Rodriguez


 
 
 

HELP - Sorting by date, priority, hour problem

Post by Kalen Delane » Wed, 16 Sep 1998 04:00:00


Nope, this is not true. Internally, datetime values are stored completely
independently of the locale, or any regional settings.  It is only when you
try to return a date that it is displayed in a locale-dependent format.

Internally, datetimes are stored in two parts:
1) the number of days before or after January 1, 1900
2) the number of milliseconds after midnight

Smalldatetimes are similar, but the number of days can only be positive
(after January 1, 1900) and the time is stored only as a number of minutes
after midnight.

If you sort by a column that is actually a datetime, it will sort
chronologically.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>1) SQL Server v 1.1 thru 7 stores the date an time in the format based upon
>the language set of the database. in us english sort orders and language
>pages it is MMM dd YYYY HH:MM:SS mmm am/pm.  this is a specific datatype
but
>can be extracted using the convert function or the datepart function which
>allows you to utilize a specific component of the datetime result. the
>datepart function returns the datepart in the datatype of the component
>(e.g. jun will be char by default, 1998 will be int, etc.)

>2) SQL Server is functionally based on character set and sort order.  The
>functions that return the data do not change functionally

>3) your procedure should contain the sort by datefield, priorityfield, and
>datepart(hh, datefield)

>enjoy
>Greg Cirino


>Hello,

>I am new to ASP and SQL server 6.5. I have a few questions.

>1) How is a datetime datatype stored in SQL server 6.5? Is it stored as a
>string of characters or as an object?

>2) Is the stored value dependent on the server language setup (for example
>US english server stores in english) or is it independent?

>3) We want to sort by date, priority and hour, where the date and hour come
>from the same datetime field, and the priority is an integer. Is it better
>to do it at the SORT BY clause level or to sort an array of the resulting
>recordset using VBScript date functions?

>Thanks for any input on this, I would really appreciate any ideas.

>Fabian Rodriguez


 
 
 

HELP - Sorting by date, priority, hour problem

Post by Kalen Delane » Wed, 16 Sep 1998 04:00:00


Nope, this is not true. Internally, datetime values are stored completely
independently of the locale, or any regional settings.  It is only when you
try to return a date that it is displayed in a locale-dependent format.

Internally, datetimes are stored in two parts:
1) the number of days before or after January 1, 1900
2) the number of milliseconds after midnight

Smalldatetimes are similar, but the number of days can only be positive
(after January 1, 1900) and the time is stored only as a number of minutes
after midnight.

If you sort by a column that is actually a datetime, it will sort
chronologically.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>1) SQL Server v 1.1 thru 7 stores the date an time in the format based upon
>the language set of the database. in us english sort orders and language
>pages it is MMM dd YYYY HH:MM:SS mmm am/pm.  this is a specific datatype
but
>can be extracted using the convert function or the datepart function which
>allows you to utilize a specific component of the datetime result. the
>datepart function returns the datepart in the datatype of the component
>(e.g. jun will be char by default, 1998 will be int, etc.)

>2) SQL Server is functionally based on character set and sort order.  The
>functions that return the data do not change functionally

>3) your procedure should contain the sort by datefield, priorityfield, and
>datepart(hh, datefield)

>enjoy
>Greg Cirino


>Hello,

>I am new to ASP and SQL server 6.5. I have a few questions.

>1) How is a datetime datatype stored in SQL server 6.5? Is it stored as a
>string of characters or as an object?

>2) Is the stored value dependent on the server language setup (for example
>US english server stores in english) or is it independent?

>3) We want to sort by date, priority and hour, where the date and hour come
>from the same datetime field, and the priority is an integer. Is it better
>to do it at the SORT BY clause level or to sort an array of the resulting
>recordset using VBScript date functions?

>Thanks for any input on this, I would really appreciate any ideas.

>Fabian Rodriguez


 
 
 

HELP - Sorting by date, priority, hour problem

Post by Steve Robinso » Wed, 16 Sep 1998 04:00:00


Gorkem ERCAN,

Quote:>   Only the date format output changes

If the IIS server  is set to american then the dates will be sent to SQL
Server in an american format. This is not dependant on how the SQL server is
set

eg  IIS will this 1.4.98 = jan 4th 1998 not 1st April 1998

also

if your clients are set to US which you may have no control over then using
the vbscript functions will give you the problem where 10/9/98  is greater
than 4/10/98 !!!

I have just finished creating a web site where dates were critical and I
found you should

    1. Set the SQL Server to the country you require
    2. Set the IIS Server to the country you require
    3. Don't rely on the clients unless you can force them to all be set
accordingly

let SQL Server do the sorting though and here is a little script to show how
it could be done

drop table testall
go
create table testall (
dates datetime,
priority int,
descr varchar(30)
)
go

insert into testall values ("1/1/98 12:00",2,"test 1")
insert into testall values ("1/2/98 12:00",2,"test 1")
insert into testall values ("1/2/98 13:00",1,"test 1")
insert into testall values ("1/2/98 14:00",3,"test 1")
insert into testall values ("1/3/98 12:00",2,"test 1")
go

select * from testall order by datepart(dd,dates), priority,
datepart(hh,dates)
go

Steve Robinson
SQLServer MVP




>>Hello,

>>I am new to ASP and SQL server 6.5. I have a few questions.

>>1) How is a datetime datatype stored in SQL server 6.5? Is it stored as a
>>string of characters or as an object?

>    It is not stored as a string of characters but SQL convert function can
>convert it.

>>2) Is the stored value dependent on the server language setup (for example
>>US english server stores in english) or is it independent?

>   Only the date format output changes
>>3) We want to sort by date, priority and hour, where the date and hour
come
>>from the same datetime field, and the priority is an integer. Is it better
>>to do it at the SORT BY clause level or to sort an array of the resulting
>>recordset using VBScript date functions?

>    In my opinion it is better to do it on the SQL server because it is why
>we have Data servers for

>>Thanks for any input on this, I would really appreciate any ideas.

>>Fabian Rodriguez


 
 
 

1. HELP - Sorting by date, priority and time problem

Hello,

I am new to ASP and SQL server 6.5. I have a few questions.

1) How is a datetime datatype stored in SQL server 6.5? Is it stored as a
string of characters or as an object?

2) Is the stored value dependent on the server language setup (for example
US english server stores in english) or is it independent?

3) We want to sort by date, priority and time, where the date and time come
from the same datetime field, and the priority is an integer. Is it better
to do it at the SORT BY clause level or to sort an array of the resulting
recordset using VBScript date functions?

Thanks for any input on this, I would really appreciate any ideas.

Fabian Rodriguez

2. Sybase Dataserver

3. Returning a date in a 24 hour format with Hours and Minutes, no seconds

4. Help file for ADO 2.5

5. Add day/hour/half-hour to given date in PL/SQL

6. Newbie question: Connecting to Oracle via OleDb

7. date sorting problem ; Please HELP

8. Is it possible to schedule checkpoints?

9. transform date to date day and hours

10. Hours and Minutes to Hours Decimal = Need Help

11. HELP: Selecting 3 dates in 24 hours period

12. Change process priority / task priority permanentely

13. Replicatoin-Date not Priority Based