Change all dates to the day before in SQL

I have an issue with a report, in that it shows the date of when sales were posted, but they were actually processed the previous day.  Therefore, I would really like to display the previous date not the date that the sale was recorded.

Dates in my table are in the format YYYYMMDD

How can I change the data via a formula to the proper previous day.  (ie 20150901 would change to 20150831 and likewise for March 1 and the proper Feb 29/or 28th.

I am creating a view in SQL so as to not disturb the actual data in the original table.

Thanks very much
Jim SmuldersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are dates really stored as string? In that case we need to convert to datetime and back:
select convert(varchar(8), dateadd(day, -1, convert(datetime, yourdate, 112)), 112) ...

Open in new window

if your app expects a datetime, you can leave out the outer convert, of course, but it does no harm.
0
Jim SmuldersAuthor Commented:
Just a question as i add this to the view, as I am not a great SQLer....how do a phrase it within the current view.   The field is called JRNLDATE.  You can see my attempt below, but of course I have botched it.

SELECT        dbo.GLPOST.ACCTID, dbo.GLPOST.FISCALYR, dbo.GLPOST.FISCALPERD,
                         dbo.GLPOST.COMPANYID, dbo.GLPOST.JNLDTLDESC, dbo.GLPOST.JNLDTLREF, dbo.GLPOST.TRANSAMT, dbo.vw_Accounts.ACSEGVAL02 AS STORE,
                         'JRNLDATE convert(varchar(8), dateadd(day, -1, convert(datetime, yourdate, 112)), 112)' AS NEWDATE
FROM            dbo.GLPOST INNER JOIN
                         dbo.vw_Accounts ON dbo.GLPOST.ACCTID = dbo.vw_Accounts.ACCTID
WHERE        (dbo.GLPOST.ACCTID > '41350000') AND (dbo.GLPOST.ACCTID < '41359999')
0
Jim SmuldersAuthor Commented:
When I try this, I get an arithemetical error:

SELECT        dbo.GLPOST.ACCTID, dbo.GLPOST.FISCALYR, dbo.GLPOST.FISCALPERD, dbo.GLPOST.SRCECURN, dbo.GLPOST.SRCELEDGER, dbo.GLPOST.SRCETYPE,
                         dbo.GLPOST.POSTINGSEQ, dbo.GLPOST.CNTDETAIL, dbo.GLPOST.AUDTDATE, dbo.GLPOST.AUDTTIME, dbo.GLPOST.AUDTUSER, dbo.GLPOST.AUDTORG,
                         dbo.GLPOST.JRNLDATE, dbo.GLPOST.BATCHNBR, dbo.GLPOST.ENTRYNBR, dbo.GLPOST.TRANSNBR, dbo.GLPOST.EDITALLOWD, dbo.GLPOST.CONSOLIDAT,
                         dbo.GLPOST.COMPANYID, dbo.GLPOST.JNLDTLDESC, dbo.GLPOST.JNLDTLREF, dbo.GLPOST.TRANSAMT, dbo.vw_Accounts.ACSEGVAL02 AS STORE,
                         CONVERT(varchar(8), DATEADD(day, - 1, CONVERT(datetime, dbo.GLPOST.JRNLDATE, 112)), 112) AS NEWDATE
FROM            dbo.GLPOST INNER JOIN
                         dbo.vw_Accounts ON dbo.GLPOST.ACCTID = dbo.vw_Accounts.ACCTID
WHERE        (dbo.GLPOST.ACCTID > '41350000') AND (dbo.GLPOST.ACCTID < '41359999')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
SELECT        dbo.GLPOST.ACCTID, dbo.GLPOST.FISCALYR, dbo.GLPOST.FISCALPERD, dbo.GLPOST.SRCECURN, dbo.GLPOST.SRCELEDGER, dbo.GLPOST.SRCETYPE,
                         dbo.GLPOST.POSTINGSEQ, dbo.GLPOST.CNTDETAIL, dbo.GLPOST.AUDTDATE, dbo.GLPOST.AUDTTIME, dbo.GLPOST.AUDTUSER, dbo.GLPOST.AUDTORG,
                         dbo.GLPOST.JRNLDATE, dbo.GLPOST.BATCHNBR, dbo.GLPOST.ENTRYNBR, dbo.GLPOST.TRANSNBR, dbo.GLPOST.EDITALLOWD, dbo.GLPOST.CONSOLIDAT,
                         dbo.GLPOST.COMPANYID, dbo.GLPOST.JNLDTLDESC, dbo.GLPOST.JNLDTLREF, dbo.GLPOST.TRANSAMT, dbo.vw_Accounts.ACSEGVAL02 AS STORE,
                         DATEADD(DAY, -1, CAST(dbo.GLPOST.JRNLDATE AS varchar(8))) AS NEWDATE
FROM            dbo.GLPOST INNER JOIN
                         dbo.vw_Accounts ON dbo.GLPOST.ACCTID = dbo.vw_Accounts.ACCTID
WHERE        (dbo.GLPOST.ACCTID > '41350000') AND (dbo.GLPOST.ACCTID < '41359999')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are you certain the "date" is valid for all rows, or do you some kind of "no value" value?
0
PortletPaulfreelancerCommented:
It is a curious design to have dates stored as a string YYYYMMDD and that string is 24 hours incorrect too

Can you look at the actual table definition and let us know what data type of column JRNLDATE is please.

e.g. what does this reveal about the column?

exec sp_columns GLPOST
0
Scott PletcherSenior DBACommented:
I would think it's an int value, since that would take less space.  That's a quite common way of storing dates as well.  [My code would work for an int or a varchar.]
0
Jim SmuldersAuthor Commented:
Hi, the field JRNLDATE comes up as (decimal(9,0), not null)

Is this sufficient info?
0
Scott PletcherSenior DBACommented:
Yes, that is sufficient.  Did my code not work? Id: 40955141
0
Jim SmuldersAuthor Commented:
Scott, thanks, that did help...however one last question.  in coming back as a result, because the dates are now stated as real dates, they come back formatted as:

2013-09-30 00:00:00.000


is there a way to trim off the time portion of the date?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I would use
 convert(varchar(8), dateadd(day, -1, convert(datetime, cast(dbo.GLPOST.JRNLDATE as varchar(8)), 112)), 112)
 as NEWDATE

Open in new window

to convert the output into a varchar. You could convert that back to an int / decimal if needed.
0
PortletPaulfreelancerCommented:
Please take note for future questions regarding "dates and times" that it is important for us to know what the column data types actually are.

"Dates in my table are in the format YYYYMMDD "
(could have been a string or integer, or even a date/datetime)
0
Scott PletcherSenior DBACommented:
Yes.  The easiest and cleanest way is to cast to a date and let the default format handle the output:

CAST(DATEADD(DAY, -1, CAST(dbo.GLPOST.JRNLDATE AS varchar(8))) AS date) AS NEWDATE

If you want a specific format, you use CONVERT:

CONVERT(varchar(10), DATEADD(DAY, -1, CAST(dbo.GLPOST.JRNLDATE AS varchar(8))), <format_code>)

Check in Books Online for the format codes and what they output.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.