Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ SQL Get Date From A String Or Long Integer

Posted by: stevep Nov 23 2019, 04:08 PM

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.

Posted by: June7 Nov 23 2019, 04:17 PM

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,

Posted by: GroverParkGeorge Nov 23 2019, 04:45 PM

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

Posted by: GroverParkGeorge Nov 23 2019, 04:50 PM

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 " datetime" so perhaps this will work for you as well.

Posted by: stevep Nov 23 2019, 05:04 PM

Here is the SQL code.

                         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.

Posted by: GroverParkGeorge Nov 23 2019, 05:06 PM

"... "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.

Posted by: GroverParkGeorge Nov 23 2019, 05:14 PM

try: CAST(decimalDate AS nvarchar(8))

Posted by: stevep Nov 23 2019, 05:20 PM

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

Posted by: stevep Nov 23 2019, 05:22 PM

try: CAST(decimalDate AS nvarchar(8))
hope I got that right

Bingo!!!! smile.gif

Edit: Spoke too son. Although it didn't error out, it just seems to return what I already had like 20191023.

Posted by: GroverParkGeorge Nov 23 2019, 05:33 PM

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

Posted by: GroverParkGeorge Nov 23 2019, 05:38 PM

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

Posted by: stevep Nov 23 2019, 05:47 PM

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 smile.gif

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.

Posted by: GroverParkGeorge Nov 23 2019, 05:54 PM

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.

Posted by: MadPiet Nov 23 2019, 06:50 PM

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

Posted by: jleach Nov 23 2019, 08:37 PM

Here's my test:

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

-- cast strinng to datetime

-- cast the int to a string, then do the same:

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

Posted by: jleach Nov 23 2019, 08:39 PM

Forgot your "sometimes may be 0"

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

Posted by: CaptainMilly Dec 2 2019, 12:10 PM

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

Posted by: AlbertKallal Dec 2 2019, 03:20 PM

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:

select id, Customer_ID, InvoiceNumber,Amount,
         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)
      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.

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

Posted by: stevep Jan 9 2020, 06:47 PM

Oops I didn't check back in to see about my thread until now.

Alberts solution gave me exactly what was requested. The output is indeed a real date and I see how I can use >GETDATE()-10 to do date filtering as desired.

is there some way to encapsulate this into a function so I don't have to repeat that Case coding every time I want to use this? I have never done an SQL function (is that what stored procedure means?)

Posted by: AlbertKallal Jan 18 2020, 07:43 PM

Thanks - I also think my example was/is easy on the eyes.

And Yes, you can create t-SQL function. And JUST like in Access you can use a VBA function in ANY SQL you write?

So, this:

select id, Customer_ID, InvoiceNumber,Amount,
         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)
      As MyInvoiceDate
      from Invoices


select id, Customer_ID, InvoiceNumber,Amount, dbo.caDate([invoiceDate]) as MyInvoiceDate
      from Invoices

The only issue of course is do you have permissions and right to modify (add) such a function to the sage database?

So, just like in Access there are:
Subs - that you call
Functions - can be used as a expression - even in SQL

In T-SQL we have:
Store Procedures - subs that you can call + use
Scaler Functions - these are just like VBA functions - once added, then any expression in code (or SQL) can use that function. There are several other types of functions in t-SQL (such as table value - returns a table). So they have several different types (names). But if you just create a function, that that is what we call a scaler value function.

The t-SQL function for above would look like:

    @d sql_variant
RETURNS datetime
    -- Declare the return variable here
    DECLARE @MyDate datetime

       SET @MyDate =
         when @d is null then cast(null as datetime)
         when @d = 0 then cast(0 as datetime)
         else cast(cast(@d as nvarchar) as datetime)
    -- Return the result of the function
    RETURN @MyDate

So, if you have permissions to that database, then you can add this function, and then in any t-SQL query you write, you can use that expression in place of the more difficult case statement.

I often create some common used VBA functions I been using in my Access/VBA SQL, and create the SAME function for T-SQL..

For example, I had a GST() function in Access to calc the tax rate, and when moved the database to t-SQL, I added this same function to "help me" move and write access queries as t-SQL. The ONLY down side is you MUST use

dbo.FuncName() and you can't use FuncName. So as peR above I had to use "dbo." in front of the function name. So use dbo.caDate( field ) as opposed to caDate().

I REALLY wish I could eliminate the need for the "dbo." part - as then a lot of a my Access SQL would have 100% worked as t-SQL without any changes. Not a huge deal, but if someone knows how to eliminate the need for the dbo.prevfix, then a lot of my access SQL would go up to SQL server without any required changes.

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

Posted by: FrankRuperto Jan 18 2020, 08:58 PM

I don't think you can remove dbo because that's the schema name and a table always has to belong to a schema. You cant have a table with no schema, but since dbo is the default schema name, if the user schema name is also dbo (by default), then you can omit using the dbo prefix and just use the function name. You can also just use [tblName] instead of [dbo].[tblName]. You could also rename the table's schema name with ALTER SCHEMA to match the user schema name if its not the default dbo name.