Link to home
Start Free TrialLog in
Avatar of Jim Smulders
Jim Smulders

asked on

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
SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Smulders
Jim Smulders

ASKER

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')
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')
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Are you certain the "date" is valid for all rows, or do you some kind of "no value" value?
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
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.]
Hi, the field JRNLDATE comes up as (decimal(9,0), not null)

Is this sufficient info?
Yes, that is sufficient.  Did my code not work? Id: 40955141
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
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.