How to create procedure between two dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to create procedure between two dates

Author

Topic

nikoz
Yak Posting Veteran

63 Posts

Posted - 2014-02-24 : 15:23:35

How to create procedure between two dates to update one colum.
I started with these code

declare @datumOd datetime, @datumDo datetime
set @datumOd='2011-02-02 14:51:45.790'
set @datumDo='2011-01-13 00:00:00'
update p
set p.opomba=''
from promet p
where p.POSLDOG in ('2800','2810') and
p.DATUMZAP between @datumOd and @datumDo
order by 3

So i habe problem with dates. My column DATUMZAP look like this
2011-01-10 09:47:56.600

IS it posible to declare dates like 2011-01-10 and to read column DATUMZAP

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-02-24 : 15:43:45

Declare it as type DATE. Or convert to type DATE.

djj

nikoz
Yak Posting Veteran

63 Posts

Posted - 2014-02-24 : 15:46:14

Declared as DATE. To convert is not a option....

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-02-24 : 18:57:06

1) What problem do you have with the date?
2) DATUMZAP cannot be declared as a DATE datatype since it has a time portion (09:47:56.600)
3) You could use the CONVERT function in your code to change DATUMZAP from a datatype of DATETIME to a datatype of DATE
4) When you compare a DATE to a DATETIME, such as @datumOd and @datumDo, the DATE gets changed to a DATETIME
5) Why do you have the "order by 3" in your UPDATE statement?

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-02-24 : 20:21:32

Your "between" values are backward; the smaller value must be first for between to work correctly.

Also, it's best to use varchar for the datetime values and let SQL convert the input value.

Finally, the only 100% safe date format is 'YYYYMMDD [time]'. Otherwise, date settings could affect how SQL treats the value.

Combining all that, here is the final query:



declare @datumOd varchar(30), @datumDo varchar(30)

set @datumOd = '20110202 14:51:45.790'
set @datumDo = '20110113 00:00:00'

update p
set p.opomba=''
from promet p 
where p.POSLDOG in ('2800','2810') and 
p.DATUMZAP between @datumDo and @datumOd

nikoz
Yak Posting Veteran

63 Posts

Posted - 2014-02-25 : 01:14:20

I slove the problem when i with corect dates. Just like ScottPLetcher explain. Thanks gys!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:53:18

No need to change the datatypes, because that can give unwanted side effects in other parts of the application.

declare	@datumOd datetime,
	@datumDo datetime

set @datumOd='2011-02-02 14:51:45.790'
set @datumDo='2011-01-13 00:00:00'

update	promet
set	opomba=''
where	POSLDOG in ('2800','2810')
	and DATUMZAP >= @datumOd
	and DATUMZAP < Dateadd(day,1 , @datumDo)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-02-25 : 12:16:00

quote:
Originally posted by SwePeso

No need to change the datatypes, because that can give unwanted side effects in other parts of the application.
declare @datumOd datetime,
@datumDo datetime

set @datumOd='2011-02-02 14:51:45.790'
set @datumDo='2011-01-13 00:00:00'

update promet
set opomba=''
where POSLDOG in ('2800','2810')
and DATUMZAP >= @datumOd
and DATUMZAP < Dateadd(day,1 , @datumDo)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Then you're assuming and requiring that column DATUMZAP have a datatype of datetime. That may or may not be true now or in the future. You could force an implicit conversion on the table column, causing conversion errors and/or very poor performance.

If the variable needs to be datetime, then convert it to character in the WHERE clause before comparing it to the column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 12:35:26

It already have an implicit conversion if DATUMZAP is character column.
If DATUMZAP is datetime, everything is working ok.

DATETIME has higher priority than character. If conversion takes place, the only downside is that the index will not be used.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-02-25 : 13:14:12

quote:
Originally posted by SwePeso

It already have an implicit conversion if DATUMZAP is character column.
If DATUMZAP is datetime, everything is working ok.

DATETIME has higher priority than character. If conversion takes place, the only downside is that the index will not be used.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

If DATUMZAP is a character column, there is not an implicit conversion required if you compare it to a character value. You're causing an implicit conversion by forcing the comparing value to be a datetime. You could also cause conversion errors, since the characters in the column may not be a valid datetime. By explicitly forcing a datetime comparison, then only if DATUMZAP actually is a datetime are you ok.

Yes, not using an index is the main (not the only) issue, but that could mean millions of additional I/Os.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources