[X]My Assistant

[stevep]

I am sure this is a trivial question for the pros.

We moved our Sage 300 ERP system (aka Accpac) from its previous platform to SQL in 2016. I am not IT, just a power user who is dabbling in SQL views so that users can get preconfigured extractions into Excel. We have no IT here, I am considered the go to person, crazy.

Accpac stores the date like 20191023. I think it is a long integer but it might be a string. Sometimes the value can be 0 representing no date (like "date shipped" when it hasn't shipped yet).

I want to covert this to a real date (not time if I can help it). All the users are converting after the extraction but I wanted to have it come over as a date in the first place, plus I just want to learn how to do this.

Things that don't work (I got them from google searches):

cast(dbo.oeordh.orddate as datetime) as DateOrd - the message is "CONVERT may be unnecessary"
convert(dbo.oeordh.orddate, getdate(),23) as DateOrd - keeps converting my field to "dbo" so I assume I am screwing up the brackets, I assume getdate() is a built in function

Although it would be nice to cover the case where the date is zero, that is not essential for my current purpose. There is always a "date ordered". But it would be nice to return a zero or empty string

There seems to be a million links to SQL resources, if there is an obvious bible or leader I would be interested to know that. I bought a few books but none seem to cover stuff like this.

I am also interested in creating and deploying functions. Stored procedures are way over my head though.

[June7]

You should be able to identify the field type in table design.

Can't you change the field type in SQL table to Date?

Update all those 0 entries to Null.

What is the frontend - Access, Excel, VB.net?

This post has been edited by June7: Nov 23 2019, 04:27 PM

[GroverParkGeorge]

You may have to parse and reassemble the characters into a "date-like" format if it is a string.

[GroverParkGeorge]

Actually, in SQL Server 2016, I was able to do this using:

select Cast(pseudodate as date) AS DateConvertedfromString, realdate from tblPseudodate

I see that you had tried "...as datetime" so perhaps this will work for you as well.

[stevep]

Here is the SQL code.

CODE

SELECT        dbo.OEORDH.ORDUNIQ, SUBSTRING(CAST(dbo.OEORDH.ORDDATE AS varchar), 3, 4) AS Period, CAST(dbo.OEORDH.EXPDATE AS date) AS DateExp, dbo.OEORDH.ORDDATE, dbo.OEORDH.ORDNUMBER,          dbo.OEORDH.CUSTOMER, dbo.OEORDH.CUSTGROUP, dbo.OEORDH.BILNAME, dbo.OEORDH.SHPCITY, dbo.OEORDH.PRICELIST, dbo.OEORDH.SHPSTATE, dbo.OEORDH.PONUMBER, dbo.OEORDH.EXPDATE, dbo.OEORDH.REFERENCE, dbo.OEORDH.NUMINVOICE, dbo.OEORDH.LASTINVNUM, dbo.OEORDH.ONHOLD, dbo.OEORDH.[DESC], dbo.OEORDH.COMMENT, dbo.OEORDH.ORDTOTAL, dbo.OEORDH.ORDLINES, dbo.OEORDH.SALESPER1, dbo.OEORDH.INVDATE, dbo.OEORDH.INVNUMBER, dbo.OEORDH.COMPDATE, dbo.OEORDH1.REQUESDATE
FROM            dbo.OEORDH INNER JOIN
                         dbo.OEORDH1 ON dbo.OEORDH.ORDUNIQ = dbo.OEORDH1.ORDUNIQ
WHERE        (dbo.OEORDH.COMPDATE = 0)

error is "Explicit conversion from data type decimal to date is not allowed". The first calculated field turned out ok (just grabbed the last 4 characters of the field)

June7, I cannot change the table definitionss in the ERP system, nor am I changing the data. I am just creating views that the users can point to in Excel for extraction. The view makes a subset of the fields in the table, and limits the records. The users have a data connection to this view from Excel to Excel tables. I don't want them poking around raw data.
This post has been edited by stevep: Nov 23 2019, 05:06 PM

[GroverParkGeorge]

"... "Explicit conversion from data type decimal to date is not allowed" "

So it's not a string. It's a decimal. Let's see if we can manage that. I'm going to have to test.

[GroverParkGeorge]

try: CAST(decimalDate AS nvarchar(8))

[stevep]

Should I just create a function for this and encapsulate everything there? If successful, I would deploy this in dozens of places.

It would be become MyDate(AccPacDate) as MyNewFieldName

I would also like to do date math in SQL like MyNewFieldName > date()-90 to get the last 90 days of whatever

[stevep]

try: CAST(decimalDate AS nvarchar(8))

hope I got that right

Bingo!!!!

Edit: Spoke too son. Although it didn't error out, it just seems to return what I already had like 20191023.
This post has been edited by stevep: Nov 23 2019, 05:26 PM

[GroverParkGeorge]

Yes, but my version of SSMS now treats it as a date, so I assumed it might work.

 datefromdecimal.png ( 34.93K )Number of downloads: 2

[GroverParkGeorge]

Another option, one that I first thought of, is to parse the components out of the "decimal Date" and recombine them using DateFromParts

 datefromparts.png ( 15.55K )Number of downloads: 1

[stevep]

George thanks for your help so far, especially on a Saturday. It is possible that you have already solved my problem.

I am going to give this a rest for today and do some testing tomorrow. It turned out more complicated than I expected (I thought I just had the brackets wrong

And even if it works, I have to get to the next step of limiting data to something like date()-90 with whatever the SQL syntax is for date expressions.

[GroverParkGeorge]

Sometimes that break in the action serves as a marination period that results in a solution. TSQL has a DateDiff() function similar, albeit not identical in syntax, to the one in Access.

[MadPiet]

just wondering, why would you use double-byte (NVARCHAR) instead of regular VARCHAR?

[jleach]

Here's my test:

CODE

-- int or string version of the value
DECLARE @DateAsInt INT = 20191123;
DECLARE @DateAsChar NVARCHAR(8) = '20191123'

-- cast strinng to datetime
SELECT CONVERT(DATE, @DateAsChar, 104);
SELECT CONVERT(DATETIME2, @DateAsChar, 104);

-- cast the int to a string, then do the same:
SELECT CONVERT(DATE, CAST(@DateAsInt AS NVARCHAR), 104);
SELECT CONVERT(DATETIME2, CAST(@DateAsInt AS NVARCHAR), 104);

-- thus:
SELECT
    SomeField,
    SomeOtherField,
    CONVERT(DATE, YourStringDateField, 104) AS ActualDateField
FROM blah

[jleach]

Forgot your "sometimes may be 0"

CODE

--DECLARE @Value NVARCHAR(8) = '20191123'
DECLARE @Value NVARCHAR(8) = 0
SELECT
    CASE WHEN @Value = 0 THEN NULL ELSE CONVERT(DATE, @Value, 104) END AS ActualValue

[CaptainMilly]

This is completely random, but sometimes when I have an issue with data conversion I export to csv and reimport and it gets fixed.

[AlbertKallal]

I will say no matter how one slices and dices this?

One wants the result to be a datetime that Access sees as a datetime.

If we miss this goal, then the access client side will not have use of date functions, like month() etc. This is often required for reports. And if client date settings are different, then we don’t care about the display of the date, but only that access sees this as a date.

Access client side does NOT have a date only datatype. If you link t SQL server, then access will only see datetime columns as an actual date column. In fact, “date” columns are seen by access as text.

So, I would as a great goal ensure that no matter what, this is viewed as the access client as a datetime.

I suggest something like this:

CODE

select id, Customer_ID, InvoiceNumber,Amount,
      case
         when invoiceDate is null then cast(null as datetime)
         when invoiceDate = 0 then cast(0 as datetime)
         else cast(cast(InvoiceDate as nvarchar) as datetime)
     END
      As MyInvoiceDate
      from Invoices

The time portion will be 0, and that don’t matter, since that is what and how we dealt with date columns in access since day one. Any date format we have in access client reports, or whatever ALWAYS had the time portion - it useally 0, and thus will not display in client side unless we format the date column to display as such. This approach ensures that issues dont' crop up due to different client side "regional" settings. All we care here is access sees this as a date column - and at that point then we can slice and dice as we please. And if we create this view on the server side, then once again the server side t-SQL date commands datediff() etc. can be used freely.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada