datepart(weekday,getdate())
i've asked a number of questions today --- thank you for all of the input, today is pretty busy, the assistance is appreciated
i have a table i am selecting from into a new table every night
at all times i want to retain the current 7 days. like if it's Tuesday, i want all the data in there from last tuesday forward
right now the relevant portion of my job just does this:
select * into newtable
from sourcetable
where timefield >=confer(varchar,dateadd(d
then i go about all the rest of the job and do my thing. old and newtable are renamed...
today, however, i just found that the maintable only contains data from the 29th and 30th.
for the life of me, i can't see how this could have happened
surely there was something in there i missed somewhere.... but, going forward, am i grabbing that data properly? i feel as though i'm not properly accounting for the weekends.
am i?
i have a table i am selecting from into a new table every night
at all times i want to retain the current 7 days. like if it's Tuesday, i want all the data in there from last tuesday forward
right now the relevant portion of my job just does this:
select * into newtable
from sourcetable
where timefield >=confer(varchar,dateadd(d
then i go about all the rest of the job and do my thing. old and newtable are renamed...
today, however, i just found that the maintable only contains data from the 29th and 30th.
for the life of me, i can't see how this could have happened
surely there was something in there i missed somewhere.... but, going forward, am i grabbing that data properly? i feel as though i'm not properly accounting for the weekends.
am i?
Scott Pletcher
convert(varchar, dateadd(d,-8,getdate()), 112)
Is your timefield a datetime or a varchar? Realize that since you are converting your check date to a varchar....
where timefield >=confer(varchar,dateadd(d
... your sort will be alphabetic if timefield is date stored in a varchar field.
where timefield >=confer(varchar,dateadd(d
... your sort will be alphabetic if timefield is date stored in a varchar field.
ASKER
hi scott. i will make that adjustment -- that makes sense. there's no reason to worry about the weekends, right? i'm always going to go back 7
7 days will only go back 7 days from the date AND TIME in which you run it. so running it at 7am will only get back to 7 am 7 days ago. Even if you start the job at midnight, you can still have time lag.
The below will give you the @StartDT in 12am time. But what happens tomorrow when you run it. Do you want duplicate data or are you removing the data by dropping the table every night? How do you intend to remove day 7 tomorrow when it effectively becomes day 8?
select * into newtable
from sourcetable
where timefield >=confer(varchar,dateadd(d
The below will give you the @StartDT in 12am time. But what happens tomorrow when you run it. Do you want duplicate data or are you removing the data by dropping the table every night? How do you intend to remove day 7 tomorrow when it effectively becomes day 8?
select * into newtable
from sourcetable
where timefield >=confer(varchar,dateadd(d
declare @startDt datetime
set @startDT = dateadd(d,-7,cast(getdate() as varchar(12)))
select @startDT
ASKER
timefield is smalldatetime
>> there's no reason to worry about the weekends, right? i'm always going to go back 7 <<
Correct; sorry I mistyped, should be 7 instead of 8 :-) .
Correct; sorry I mistyped, should be 7 instead of 8 :-) .
ASKER
>>>so running it at 7am will only get back to 7 am 7 days ago.
I hadn't thought of this. But, if i am convering on the day, there's no timestamp in the start time -- i run at 5pm going back 7 days, it only looks at the date 7 days ago, does it not?
I hadn't thought of this. But, if i am convering on the day, there's no timestamp in the start time -- i run at 5pm going back 7 days, it only looks at the date 7 days ago, does it not?
subtracting 7 days from now gives you exactly 7 days ago.... which is 3:34PM EST
You still have to account for the time component of datetime which is *always* present, whether it shows or not.
Note that my convert will strip the time, so it will default to 00:00 (midnight), the earliest possible time, so it will get the whole day.
Note that my convert will strip the time, so it will default to 00:00 (midnight), the earliest possible time, so it will get the whole day.
ScottPletcher:
Then he'll have to cast his date value into the same format and do an int compare though right?
Then he'll have to cast his date value into the same format and do an int compare though right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
aaah yes, understood. display and actuality are two very different things in tsql land
thank you both very much
i think the default to midnight is exactly what i need
thank you both very much
i think the default to midnight is exactly what i need
ASKER
>>Technically using a DATEADD() operation to reset the time portion to 00:00 is faster,
can you show me both, scott?
can you show me both, scott?
DECLARE @datetime DATETIME
SET @datetime = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT @datetime
SET @datetime = CONVERT(CHAR(8), GETDATE(), 112)
SELECT @datetime
SET @datetime = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT @datetime
SET @datetime = CONVERT(CHAR(8), GETDATE(), 112)
SELECT @datetime
ASKER
excellent. thank you, scott.
What abouy?
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
You can't depend on implicit conversions.
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
You can't depend on implicit conversions.
select 1
where getdate() > '20081131'
ASKER
please elaborate
So...
SET @datetime = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT @datetime
.. Uses the dateadd function to add the date difference in days between the beginning of recordable time to the beginning of recordable time to get today.
And you feel that is more clear than below?
SET @datetime = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT @datetime
.. Uses the dateadd function to add the date difference in days between the beginning of recordable time to the beginning of recordable time to get today.
And you feel that is more clear than below?
select dateadd(d,-7,cast(getdate() as varchar(12)))
Try the following SQL:
An implicit conversion is NOT done as Scott stated.
An implicit conversion is NOT done as Scott stated.
select 1
where getdate() > '20081131'
select 1
where getdate() > '20080131'
>> An implicit conversion is NOT done as Scott stated.
where getdate() > '20081131'
<<
November has only 30 days; of course the date must be valid for implicit conversion to work, just as with explicit.
>> And you feel that [dateadd method] is more clear than [this]?
select dateadd(d,-7,cast(getdate(
Yes, and safer too, since your method depends heavily on implicit conversion and SQL settings.
But, as I said before, I think the format 112 conversion is much clearer than either.
where getdate() > '20081131'
<<
November has only 30 days; of course the date must be valid for implicit conversion to work, just as with explicit.
>> And you feel that [dateadd method] is more clear than [this]?
select dateadd(d,-7,cast(getdate(
Yes, and safer too, since your method depends heavily on implicit conversion and SQL settings.
But, as I said before, I think the format 112 conversion is much clearer than either.
DOH... I meant to change it to october not november..... :((