I recently have had a project placed on my shoulders that will mostly be dealing with making the data look the way the company wants it. (I know that sounds bad, as though I'm supposed to be fudging financial data.) But not to worry this data has nothing to do with finances. The project that has been given to me is a full version of one of our web applications that is meant for demonstration purposes. The data in this project is only touched by our sales staff so it is not added to or updated in a way that is always helpful for powerful demonstration purposes. So every night we have a job run that restores the database and another job that runs to help make the data mimic what we have in our production environment so that we can wipe out any changes that the sales staff have made and so that it is kept up-to-date in a way. Like any demonstration application you want it to show the most ideal yet still realistic situations. To make the data ideal we have to edit some of the entires to get the response we are looking for. But if we do any editing then the restore will wipe out our changes. What we do to avoid this is fairly simple. Before updating the data we do a restore of the database to clean out the changes, then we apply our changes and make a new backup to use for restore. Like I said its simple. We just have to wait till later at night to do all this so that we don't disrupt the sales team. To make the data seem realistic we can not be showing potential clients the timestamps of when these records were shown if we are doing the updates late at night after normal business hours. So to off set this one of my first tasks was to adjust the times of all the entries to fall within more normal business hours. Now I fully admit I'm not an expert with SQL or even really that good. I'd like to think that I've been picking it up fairly quickly while on the job but there is still a lot of knowledge missing.
So back to the problem, I have to change these datetimes to be the same day but the hours need to be between 8 am and 4 pm. The first thing that came to mind was the between function. I knew you could use it for this sort of thing, but how do I get it to look at the time only ignoring the different dates. Well from as far as I could tell you can't get it to ignore the date. So I came up with something like this.
select * from table_with_records
where company_id in(company select statement)
and convert(varchar(10), creation_date, 108) < '16:00:00'
and convert(varchar(10), creation_date, 108) > '08:00:00'
order by convert(varchar(10), creation_date, 108)
This effectively gave me the records that fell with in my time range. Now what about the records that I needed to change? How do I get them to fall within this range? I know that if I changed the datetime for those that were before 8 am I could do something like
update table_with_records
set creation_date = DateAdd(hh, 8, creation_date)
from table_with_records
where company_id in(company select statement)
and convert(varchar(10), creation_date, 108) < '08:00:00'
This would work how ever I would end up with the chance of many records falling with in the last hour of business. To avoid this I made the update into two statements. Each only adding 4 hours. That way any records that were created between 4 am and 8 am would be not fall with in the second update.
In the end this is what I ended up doing.
--adjust times before 8am
update table_with_records
set creation_date = DateAdd(hh, 4, creation_date)
from table_with_records
where company_id in(company select statement)
and convert(varchar(10), creation_date, 108) < '08:00:00'
update table_with_records
set creation_date = DateAdd(hh, 4, creation_date)
from table_with_records
where company_id in(company select statement)
and convert(varchar(10), creation_date, 108) < '08:00:00'
--adjust time after 4 pm
update table_with_records
set creation_date = DateAdd(hh, -4, creation_date)
from table_with_records
where company_id in(company select statement)
and convert(varchar(10), creation_date, 108) > '16:00:00'
update table_with_records
set creation_date = DateAdd(hh, -4, creation_date)
from table_with_records
where company_id in(company select statement)
and convert(varchar(10), creation_date, 108) > '16:00:00'