How can I increment the date in a program? 2

[SQLBill]

I am running a daily job that uses the date for the where clause. I would like the job to increment the dates after it runs. Can this be done? If so, how?

The where statement is similar to this:
(read date-time as mm/dd/yy hh:mm:ss.000)

where eventdate >= '02/04/02 00:00:00.000'
and eventdate < '02/05/02 00:00:00.000'

I do a retrieval of the previous day's information, so today (5 Feb) I would be retrieving information from the 4th. Then I would like the dates to increment so that when the job runs tomorrow it has the correct dates.


Any suggestions?

-Bill

 

[tlbroadbent]

Use the system date rather than hard coding dates.

Where eventdate >= convert(char(11), getdate()-1)
And eventdate < convert(char(11), getdate()) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Terry,

I'll try your suggestion....now what if I want to pull from 05:00 to 05:00?

I normally would change the query to:

where eventdate >= '02/04/02 05:00:00.000'
and eventdate < '02/05/02 05:00:00.000'

I'm pulling from a column that has the date and time in that format (mm/dd/yy hh:mm:ss.000).

-Bill

 

[tlbroadbent]

Concatenate the times to the Where clause.

Where eventdate >=
convert(char(11), getdate()-1) + ' 05:00:00'
And eventdate <
convert(char(11), getdate()) + ' 05:00:00' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Thanks Terry,

I'll give it a try and let you know how it worked.

-Bill

 

[SQLBill]

Terry,

I tried your suggestion and it didn't work. I got the error message:

Syntax error converting datetime from character string.

I re-checked my typing against your solution and it's the same. I checked the eventdate column in the table and it's &quot;eventdate (datetime, not null)&quot;.

Should I leave the convert out and do it:
where eventdate >= (getdate()-1) + '05:00:00.000'
and eventdate < (getdate()) + '05:00:00.000'

-Bill
(I can hardly wait for my MS SQL Programming class - Feb 25th!!! Then I might know something.)

 

[tlbroadbent]

Here are two other options:

Where eventdate >=
convert(char(10), getdate()-1, 121) + ' 05:00:00'
And eventdate <
convert(char(10), getdate(), 121) + ' 05:00:00'

Where eventdate >=
convert(datetime,convert(char(10), getdate()-1, 121) + ' 05:00:00')
And eventdate <
convert(datetime,convert(char(10), getdate(), 121) + ' 05:00:00')

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Terry,

I tried both of your options, neither worked. In both cases I got the error message:
Syntax error converting datetime from character string.

So, I've been using the BOL and whenever I see or get a program suggestion I start looking up how it works in BOL. That gave me some ideas to try.

I tried:
Where eventdate >=
convert(datetime, getdate()-1, 1)
And eventdate <
convert(datetime, getdate(), 1)

(I used the 1 instead of 121 to get the format mm/dd/yy).
That gave me everything in the select for today's date. But when I added the time:

Where eventdate >=
convert(datetime, getdate()-1, 1) + '05:00:00.000'
And eventdate <
convert(datetime, getdate(), 1) + '05:00:00.000'

It no longer worked. I didn't get an error, just got the headers and no data.

I then tried:

Where eventdate >=
'02/06/02 13:00:00.000'
And eventdate <
'02/06/02 14:00:00.000'

It returned a lot of records. I picked one single record and it had a time of 13:39:45.000.

I then ran:

Where eventdate =
'02/06/02 13:39:45.000'

and I got that single record returned.

Then I ran:

Where eventdate >=
convert(datetime, getdate()-1, 1) + '13:39:45.000'

and again I only got the headers and no data.

Any more ideas for me on this? Do I need to retrieve the system date and truncate it before I add the time? If so, what's the syntax?

-Bill

 

[tlbroadbent]

You are converting to datetime and concatenating a string. You need to convert the date to varchar or char.

Where eventdate >=
convert(char(8), getdate()-1, 1) + '05:00:00.000'
And eventdate <
convert(char(8), getdate(), 1) + '05:00:00.000' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tombos]

You could also use something like dateadd(d,1,getdate()), which would retrieve tomorrow.

Likewise, dateadd(d,-1,getdate()) would retrieve yesterday.

 

[tlbroadbent]

Dateadd can be used to increment or decrement dates but it still returns the time as well as the date. The issue of truncating the time portion of a datetime field still has to be addressed. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Terry,

I tried your latest suggestion and it failed. However, I play around with your suggestions and my script and this time I got it to work! YEA! Here's the solution:

Where eventdate >=
convert(char(12), getdate()-1, 1) + '05:00:00.000'
And eventdate <
convert(char(12), getdate(), 1) + '05:00:00.000'

Nothing worked until I changed it to char(12).

So thank you for the help, you led me down the path to the proper solution. But most of all you made me think about what your suggestions did and why they didn't do what I needed.

18 more days until I take the MS SQL Programming class!

-Bill

 

[tlbroadbent]

I don't know why you needed to convert to char(12). Style 1 is only 8 characters long - mm/dd/yy. char(8) should work or char(10) should work with style 101. Actually, it may be better to use style 20 with char(8) or 120 with char(10) because those style numbers return a yy-mm-dd format. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Terry,

I finally found out why mine worked and your suggestion didn't. It's all in the space....

I played around some more and this works:

Where eventdate >=
convert(char(8), getdate()-1, 1) + ' ' + '05:00:00.000'
And eventdate <
convert(char(8), getdate(), 1) + ' ' + '05:00:00.000'


My using char(12) left in the spaces that were after the date.

-Bill

 

[tlbroadbent]

Bill,

I apologize for the error. If you examine all but my post using char(8) the extra space is included in the time string. I'll try to be more careful in the future but it won't do much good. I'm quite mistake prone.

Examples:

Where eventdate >=
convert(char(11), getdate()-1) + ' 05:00:00'
And eventdate <
convert(char(11), getdate()) + ' 05:00:00'

Where eventdate >=
convert(char(10), getdate()-1, 121) + ' 05:00:00'
And eventdate <
convert(char(10), getdate(), 121) + ' 05:00:00' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[SQLBill]

Terry,

That's ok. My not noticing that space in your suggestions ended up being a good thing for me. From seeing your posts in this forum, I knew you were very knowledgeable. So, I kept working on my problem to find out why you said your script should have worked when I couldn't get it to work properly. I have learned a lot about getdate() and datetime data type.

Yes, if I had noticed the space in your scripts my problem would have been solved earlier, but I wouldn't have learned as much.

Thanks again for your continuing help.

-Bill