Hi,
I currently have a column in a table that is a default of getdate()). My
question : Is there a way to capture just the date without the time appended
?
For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexes
on this datetimestamp field? Would a clustered index on this field help?
Almost all searching is done by the date field, whether it is searched by da
y
or a date range...Any better way than what is currently being done to help
with speed ? Currently we have a clustered index on this column. The column
currently contains approximately 12 million rows...
Thanks for your input,
WarrenChange the defualt to Cast (getdate() as Integer)
-- Datetimes as stored internally as a decimal value 4 bytes for the
integer portion, and another 4 bytes for the fractional portion. The 4 byte
s
for integer portion are the date, and the other 4 bytes are the time... So i
f
you cast the value to an integer, you truncate the time...
"Warren" wrote:
> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Store same time for all rows.
Example:
use tempdb
go
create table dbo.t (
colA int not null identity unique,
colB datetime default (convert(char(8), getdate(), 112))
)
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
select * from t
drop table t
go
If you are planning to do a lot of selects filtering this column based on a
range of dates, then having a clustered index by this column will be helpful
.
AMB
"Warren" wrote:
> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||There is no way to capture a date without the time portion; best thing to do
is probably write a function that sets each portion of the time to zero - us
e
DATEDIFF and DATEADD functions to subtract hours, minutes, seconds, and
milliseconds from the date.
With SQL 2000 32-bit datetimes are stored as 2 4-byte integers, so you can
convert the datetime to a binary(8), take the first 4 bytes and concat
0x00000000 to that and convert back to datetime to get zero hours, mins,
secs, & ms; but this method would not necessarily work on anything but SQL 2
k
32-bit.
As for a clustered index, only testing will tell...
KH
"Warren" wrote:
> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||If you're really interested in speed, don;t use DateTime column use an
integer or smallint.. and store the integer you get from Cast (getDate()
as Integer) directly. This reduces the size of the data in the column frm
8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width o
f
the index, you will dramatically increase the number of entries you will be
able to store on each IO Page of the index, and increase the performance
using the index.
NOTE: If you use smallints, then you need to subtract 32768 from the valu
you get from Cast(getdate() as Integer) before stuffing it into the smallint
field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
UNSIGNED 2-byte integer..
SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 65536
representing 6 June 2079... whereas smallint goes from -32768 to +32767...
Using Regular datetime is no problem, because it uses SIGNED 4-byte Integer,
with values from
January 1, 1753 through December 31, (this way value 0 still represents 1
Jan 1900... )
If your queries against this table often use date as a range of dates...
then a clustered index on this column will increase performance
draamatically.
"Warren" wrote:
> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Thanks for all the replys...
I found out that the client application inserts the date stamp in the row of
the database. The colun is a dattime field, so it's not the getdate()) that
I thought it was..
Any more thoughts?
Thanks,
Warren
"CBretana" wrote:
> If you're really interested in speed, don;t use DateTime column use an
> integer or smallint.. and store the integer you get from Cast (getDate(
)
> as Integer) directly. This reduces the size of the data in the column fr
m
> 8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width
of
> the index, you will dramatically increase the number of entries you will b
e
> able to store on each IO Page of the index, and increase the performance
> using the index.
> NOTE: If you use smallints, then you need to subtract 32768 from the valu
> you get from Cast(getdate() as Integer) before stuffing it into the smalli
nt
> field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
> UNSIGNED 2-byte integer..
> SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 6553
6
> representing 6 June 2079... whereas smallint goes from -32768 to +32767...
> Using Regular datetime is no problem, because it uses SIGNED 4-byte Intege
r,
> with values from
> January 1, 1753 through December 31, (this way value 0 still represents 1
> Jan 1900... )
> If your queries against this table often use date as a range of dates...
> then a clustered index on this column will increase performance
> draamatically.
> "Warren" wrote:
>|||Then you need to
a) change the way the client write the data to strip off the time portion,
How is client writing to Database? using CLient constructed SQL,
Through a Stored Proc, etc.?
b) write an Insert/ update trigger on the database table that strips off the
time portion whenver client tries t owrite time into the table
In either case, To speed things up, same comments I made earlier about
datatypes still apply...
"Warren" wrote:
> Thanks for all the replys...
> I found out that the client application inserts the date stamp in the row
of
> the database. The colun is a dattime field, so it's not the getdate()) th
at
> I thought it was..
> Any more thoughts?
> Thanks,
> Warren
> "CBretana" wrote:
>
sql
hypercode: