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,-7,getdate()),101)

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?
LVL 17
dbaSQLAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
No.  SQL will automatically convert the char value to a datetime.

I used YYYYMMDD as that is the format SQL is most likely to automatically recognize as a valid datetime.

Technically using a DATEADD() operation to reset the time portion to 00:00 is faster, but to me it less clear to someone else looking at the code, and since GETDATE() is only ever converted once anyway, I'm willing to add a few microseconds to make the code clearer.
0
 
Scott PletcherSenior DBACommented:
convert(varchar, dateadd(d,-8,getdate()), 112)
0
 
BrandonGalderisiCommented:
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,-7,getdate()),101)

... your sort will be alphabetic if timefield is date stored in a varchar field.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dbaSQLAuthor Commented:
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
0
 
BrandonGalderisiCommented:
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,-7,getdate()),101)
declare @startDt datetime
set @startDT = dateadd(d,-7,cast(getdate() as varchar(12)))
select @startDT

Open in new window

0
 
dbaSQLAuthor Commented:
timefield is smalldatetime
0
 
Scott PletcherSenior DBACommented:
>> 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 :-) .
0
 
dbaSQLAuthor Commented:
>>>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?
0
 
BrandonGalderisiCommented:
subtracting 7 days from now gives you exactly 7 days ago.... which is 3:34PM EST
0
 
Scott PletcherSenior DBACommented:
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.
0
 
BrandonGalderisiCommented:
ScottPletcher:
Then he'll have to cast his date value into the same format and do an int compare though right?
0
 
dbaSQLAuthor Commented:
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
0
 
dbaSQLAuthor Commented:
>>Technically using a DATEADD() operation to reset the time portion to 00:00 is faster,
can you show me both, scott?
0
 
Scott PletcherSenior DBACommented:
DECLARE @datetime DATETIME

SET @datetime = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT @datetime

SET @datetime = CONVERT(CHAR(8), GETDATE(), 112)
SELECT @datetime
0
 
dbaSQLAuthor Commented:
excellent.  thank you, scott.
0
 
BrandonGalderisiCommented:
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.
select 1 
where getdate() > '20081131'

Open in new window

0
 
dbaSQLAuthor Commented:
please elaborate
0
 
BrandonGalderisiCommented:
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?


select dateadd(d,-7,cast(getdate() as varchar(12)))

Open in new window

0
 
BrandonGalderisiCommented:
Try the following SQL:

An implicit conversion is NOT done as Scott stated.
select 1 
where getdate() > '20081131'
 
select 1 
where getdate() > '20080131'

Open in new window

0
 
Scott PletcherSenior DBACommented:
>> 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() as varchar(12))) <<

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.
0
 
BrandonGalderisiCommented:
DOH... I meant to change it to october not november..... :((
0
All Courses

From novice to tech pro — start learning today.