Remove timepart from datetime field, change Stored Procs.

Question

Remove timepart from datetime field, change Stored Procs.

techstuff

SSC Journeyman

Points: 80
More actions
January 6, 2005 at 9:00 pm

#66383

A Table called Hdr has a datetime field with date value and time value
ex:1997-03-05 19:16:05.000. Time value is not used for any purpose
in the application.
Below queries currently used in Stored procs.
@fromdate and @todate are char(10) field.
--value passed to parameter in ymd format, ex @fromdate='2004/01/01',@todate='2004/01/30'
no dateformat option set
select * from hdr where
convert(datetime,convert(char(10),b.transdate,101)) between @fromdate and @todate
or with
--value passed to parameter in dmy format,ex @fromdate='01/01/2004',@todate='30/01/2004'
set dateformat dmy,
select * from hdr where
convert(datetime,convert(char(10),transdate,103)) between @fromdate and @todate
Both of this works with no issues.
Does this work?
1)Remove the time part
update hdr
set transdate=convert(datetime,convert(char(10),transdate,101))
2)(with convert function removed)
--value passed to parameter in ymd format
no dateformat option set
select * from hdr where
transdate between @fromdate and @todate
or with
--value passed to parameter in dmy format
set dateformat dmy
select * from hdr where
transdate between @fromdate and @todate
The reason for the change is that, use of convert function on the datefield
causes the index to be ignored and slow query performance
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Now, if I understand you right, why do you CONVERT at all? Can't you simply change the input parameter datatypes to DATETIME?

If the table has a DATETIME column, and you want to have all rows for one month (like in your example), you can do something like:

--value passed to parameter in ymd format, ex @fromdate='2004/01/01',@fromdate='2004/02/01'

Just make sure that you query >= @fromdate AND < @todate

I assume, the double mentioning of @fromdate above is actually a typo, right?

Striping the time portion you imo only be an option if you can also change the app, too. Otherwise, this will become a regular task.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

Going along with Frank, IF you want a daterange and DON'T want to look at TIME and you don't want to use CONVERT due to performance THEN

Why not advance your TO date + 1 ????? IF you are not passing TIME in the variables this should work with no hitch....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

techstuff SSC Journeyman Points: 80 More actions · Answer 3

Reason for using convert

The application has more than 100 reports, we are using crystal reports with VB as front end, all reports will have to be changed to have datetime parameters.

Sorry for the typo, its supposed to be @todate

We have decided to remove the time value from the transdate field, only one SP updates the transdate field so changing this is will be easy, also all report SPS will be changed to use the new date query transdate between @fromdate AND @todate

Just make sure that you query >= @fromdate AND < @todate

cant i use my method of querying? after removing the time value the date field will be left only with datevalue so this query should work?

transdate between @fromdate AND @todate

Thanks.

techstuff SSC Journeyman Points: 80 More actions · Answer 4

Adding + 1 to the format is an option will consider if all else fails.

The time value will be removed from the transdate field. Please read my first post and let me know if i do that will my query work.

Thanks

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

cant i use my method of querying? after removing the time value the date field will be left only with datevalue so this query should work?

Sorry for the confusion. Sure, when there is no time portion this will work.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

techstuff SSC Journeyman Points: 80 More actions · Answer 6

Thanks frank,

To conclude this should work.

1)Remove the time part

update hdr

set transdate=convert(datetime,convert(char(10),transdate,101))

2)(with convert function removed)

--value passed to parameter in ymd format

no dateformat option set

select * from hdr where

transdate between @fromdate and @todate

or with

--value passed to parameter in dmy format

set dateformat dmy

select * from hdr where

transdate between @fromdate and @todate

Next week will be a busy week for me then

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Next week will be a busy week for me then

This is better than getting bored at work!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]