SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to create procedure between two dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nikoz
Yak Posting Veteran

57 Posts

Posted - 02/24/2014 :  15:23:35  Show Profile  Reply with Quote
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

USA
283 Posts

Posted - 02/24/2014 :  15:43:45  Show Profile  Reply with Quote
Declare it as type DATE. Or convert to type DATE.

djj
Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 02/24/2014 :  15:46:14  Show Profile  Reply with Quote
Declared as DATE. To convert is not a option....
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 02/24/2014 :  18:57:06  Show Profile  Reply with Quote
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)

Edited by - Bustaz Kool on 02/24/2014 18:57:50
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 02/24/2014 :  20:21:32  Show Profile  Reply with Quote
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

Go to Top of Page

nikoz
Yak Posting Veteran

57 Posts

Posted - 02/25/2014 :  01:14:20  Show Profile  Reply with Quote
I slove the problem when i with corect dates. Just like ScottPLetcher explain. Thanks gys!!!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/25/2014 :  11:53:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 02/25/2014 :  12:16:00  Show Profile  Reply with Quote
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.

Edited by - ScottPletcher on 02/25/2014 12:22:49
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/25/2014 :  12:35:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
279 Posts

Posted - 02/25/2014 :  13:14:12  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000