Remove timepart from datetime field, change Stored Procs.

Question

Post reply

Remove timepart from datetime field, change Stored Procs.

techstuff

Valued Member

Points: 60
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

AJ Ahrens

SSC-Insane

Points: 20676

January 7, 2005 at 5:15 am

#536366

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 Valued Member Points: 60 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 Valued Member Points: 60 More actions · Answer 4

techstuff

Valued Member

Points: 60

January 7, 2005 at 7:23 am

#536403

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 Valued Member Points: 60 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

Frank Kalis

SSC Guru

Points: 111183

January 10, 2005 at 1:43 am

#536604

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]