April 13, 2006 at 9:10 am
Why shoulkd you want to remove the time?
Typically because you have been passed a datetime but want to select data for a range of complete days.
April 13, 2006 at 9:45 am
The stored procedure inserts into multiple tables. One of the tables needs only the date as it checks if a record with that date exists (and other fields) and updates if it does and inserts when it does not. There are numerous reports that use this table based on the date only.
So the query is:
IF exists (select id from table where theDate = @theDate and otherField=@otherField...)
UPDATE table SET someOtherField = @someOtherField where theDate = @theDate and otherField=@otherFIeld
ELSE
INSERT INTO table (...)
Now, if there is time in there, I would have to do datepart on check if exists and update.
There are about 100K records written daily to that table, so it is quite large and then some reports aggregate on the dates.
April 13, 2006 at 11:50 am
Does anyone have a method for returning the time value of the datetime field? I have been doing this with a character conversion:
set @loop = 1
set @dStart = getdate()
--uses convert
while @loop <@lMax
begin
set @d= '12/30/1899 ' + Convert(char(12), getdate(), 108)
set @loop = @loop + 1
end
print 'Time Conversion - varchar'
select datediff(ms, @dStart,getdate()), @d
This returns only the time portion of the date. Playing around with the float method I come up with:
set @loop = 1
set @dStart = getdate()
--uses convert
while @loop <@lMax
begin
set @d= cast(cast(getdate() as float)-(floor(cast(getdate() as float)+2)) as datetime)
set @loop = @loop + 1
end
print 'Time Conversion - float'
select datediff(ms, @dStart,getdate()), @d
This is not the prettiest code but it runs faster than the varchar method. I am not sure how I could apply the dateadd/datediff method in a way that would return just the time.
Thoughts?
April 13, 2006 at 12:10 pm
You can use dateadd/datepart to calculate # of seconds in a day and add that to 0 date.
The below example would return: 1900-01-01 18:01:06.000
declare @x dateTime
set @x = '2006-4-13 18:01:06'
select dateadd(second, datepart(hour, @x)*3600+datepart(minute, @x)*60+datepart(second, @x), 0)
If you need ms accuracy you would have to mult. all those by additional 1000 add datepart(ms,@x) and dateadd(ms...)
April 13, 2006 at 5:19 pm
wjwGeorgia,
You can take a look at http://vyaskn.tripod.com/searching_date_time_values.htm
April 15, 2006 at 10:22 pm
I just can't bring myself to trust anything in an article where the author makes mistakes like the following...
"Remember that the BETWEEN clause retrieves values that are equal to the upper and lower limits, so you can’t code the upper limit as just '2002-02-29'. If you do, then you’ll incorrectly retrieve row 3. Another way to get the same result is to use comparison operators:
SELECT * FROM DateSample
WHERE DateVal >= '2002-02-28' AND DateVal < '2002-02-29'"
And, it's obvious author didn't test the code or he/she would have gotten the following error...
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
--Jeff Moden
April 15, 2006 at 11:13 pm
Although the DATEADD/DATEDIFF method will sometimes return a faster duration, it is slower than "convert(datetime,convert(int,GetDate()-.5))" if you consider actual resources used like CPU Seconds and DIsk Reads and Writes. Here's the results from my million row test table on a single processor box...
Method: |
CONVERT/VARCHAR
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
5733
5750
5733
5739
5623
5610
5576
5603
CPU Usage (ms)
5641
5672
5719
5677
5500
5485
5454
5480
Disk Reads/Writes
378
378
378
378
113
113
113
113
Method:
CAST/FLOOR
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
2016
2000
2016
2011
2060
1970
2050
2027
CPU Usage (ms)
1984
1968
1938
1963
1782
1813
1813
1803
Disk Reads/Writes
378
378
378
378
113
113
113
113
Method:
DATEADD/DATEDIFF
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
1796
1843
1936
1858
1030
1096
1080
1069
CPU Usage (ms)
859
828
922
870
796
844
813
818
Disk Reads/Writes
379
379
379
379
113
113
113
113
Method:
CONVERT/INT
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
1830
1860
1843
1844
950
920
953
941
CPU Usage (ms)
703
734
609
682
703
657
704
688
Disk Reads/Writes
378
378
378
378
113
113
113
113
Method:
CALCULATED COL
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
1826
1856
1826
1836
1813
1810
1796
1806
CPU Usage (ms)
1046
1109
1000
1052
969
890
907
922
Disk Reads/Writes
378
378
378
378
378
378
378
378
Method:
FUNCTION
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
9110
9236
9186
9177
8840
8813
8826
8826
CPU Usage (ms)
8953
9031
9047
9010
8719
8703
8750
8724
Disk Reads/Writes
386
386
386
386
121
121
121
121
Both sets of test had a Primary Key on an unrelated column. (Sorry for the messy display... HTML does weird things to a spreadsheet.
Here's the results on a multi-processor box with a SAN attached...
Method: |
CONVERT/VARCHAR
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
4376
4500
4500
4459
3250
3286
3296
3277
CPU Usage (ms)
3454
3360
3297
3370
3188
3250
3250
3229
Disk Reads/Writes
704
704
704
704
124
124
124
124
Method:
CAST/FLOOR
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
3920
3940
4016
3959
1093
1063
1063
1073
CPU Usage (ms)
1140
1313
1141
1198
1031
1062
1031
1041
Disk Reads/Writes
704
704
704
704
124
124
124
124
Method:
DATEADD/DATEDIFF
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
3750
3810
3783
3781
483
470
483
479
CPU Usage (ms)
593
437
766
599
485
464
485
478
Disk Reads/Writes
704
704
704
704
124
124
124
124
Method:
CONVERT/INT
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
3733
3860
3720
3771
373
376
373
374
CPU Usage (ms)
344
406
344
365
375
375
344
365
Disk Reads/Writes
704
704
704
704
124
124
124
124
Method:
CALCULATED COL
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
3796
3826
3780
3801
4106
4186
3906
4066
CPU Usage (ms)
578
672
594
615
500
609
562
557
Disk Reads/Writes
704
704
704
704
108
708
708
508
Method:
FUNCTION
WITHOUT an Index on "ADate"
WITH an Index on "ADate"
Measurement
Run 1
Run 2
Run 3
Avg
Run 1
Run 2
Run 3
Avg
Duration (ms)
5360
5343
5296
5333
4733
4796
4813
4781
CPU Usage (ms)
4781
4813
4688
4761
4625
4703
4734
4687
Disk Reads/Writes
712
712
712
712
135
135
135
135
--Jeff Moden
April 16, 2006 at 5:14 am
It's obvious that author just used not the same DATEFIRST settings on his server as you.
_____________
Code for TallyGenerator
April 16, 2006 at 5:28 am
"It's obvious that author just used not the same DATEFIRST settings on his server as you."
Actually, the date was specified as '2002-02-29', which is invalid no matter what the regional setting, because 2002 was not a leap year, and thus there is no 29-FEB-2006. (the only other alternate for intrepeting the date is month=29, which is obviously invalid as well).
The article isn't bad, but the author obviously didn't test the code.
April 16, 2006 at 5:49 am
Author must have used set of values with another year.
Because he displays the table with value '2002-02-29' in datetime column.
It's impossible.
I think originally there was another year in those examples, replaced before publishing for some reason (not to show when the article was originally written?)
_____________
Code for TallyGenerator
January 22, 2009 at 3:14 am
I have a similar problem. I have lots of tables where the time part is getting stored and need to quickly ensure only the date is stored without changing app code.
Can I add some constraint to the column so it will store only the date part?
January 22, 2009 at 5:57 am
mathur.akhil (1/22/2009)
I have a similar problem. I have lots of tables where the time part is getting stored and need to quickly ensure only the date is stored without changing app code.Can I add some constraint to the column so it will store only the date part?
Yeah, you could put a constraint on it... and cause all sorts of heartache in the app. If you could, it would be considered to be a loss of potentially important data.
As a side bar, this is the reason why stored procedures should be created to retrieve and store data in tables. Had that been done instead of allowing the APP to have direct access to the table data, this would be a no brainer because it would be a minor change to a stored procedure... instead, you need to find EVERYWHERE it is done in the APP, recompile it, retest it, and resdistribute it.
--Jeff Moden
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply