UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> SQL Get Date From A String Or Long Integer, SQL Server 2012    
 
   
stevep
post Nov 23 2019, 04:08 PM
Post#1



Posts: 107
Joined: 9-November 14



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.
Go to the top of the page
 
Start new topic
Replies
AlbertKallal
post Dec 2 2019, 03:20 PM
Post#2


UtterAccess VIP
Posts: 2,955
Joined: 12-April 07
From: Edmonton, Alberta Canada


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


Go to the top of the page
 
stevep
post Jan 9 2020, 06:47 PM
Post#3



Posts: 107
Joined: 9-November 14



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?)




Go to the top of the page
 
AlbertKallal
post Jan 18 2020, 07:43 PM
Post#4


UtterAccess VIP
Posts: 2,955
Joined: 12-April 07
From: Edmonton, Alberta Canada


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:
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


Becomes:

CODE
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:

CODE
CREATE FUNCTION caDate
(    
    @d sql_variant
)
RETURNS datetime
AS
BEGIN
    -- Declare the return variable here
    DECLARE @MyDate datetime

       SET @MyDate =
       CASE
         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)
       END
    -- Return the result of the function
    RETURN @MyDate
END


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.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canadakallal@msn.com
Go to the top of the page
 
FrankRuperto
post Jan 18 2020, 08:58 PM
Post#5



Posts: 661
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


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.
This post has been edited by FrankRuperto: Jan 18 2020, 09:29 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 

Posts in this topic
- stevep   SQL Get Date From A String Or Long Integer   Nov 23 2019, 04:08 PM
- - June7   You should be able to identify the field type in t...   Nov 23 2019, 04:17 PM
- - GroverParkGeorge   You may have to parse and reassemble the character...   Nov 23 2019, 04:45 PM
|- - GroverParkGeorge   Actually, in SQL Server 2016, I was able to do thi...   Nov 23 2019, 04:50 PM
|- - stevep   Here is the SQL code. CODESELECT dbo.OEORDH.ORDU...   Nov 23 2019, 05:04 PM
|- - GroverParkGeorge   "... "Explicit conversion from data type...   Nov 23 2019, 05:06 PM
|- - GroverParkGeorge   try: CAST(decimalDate AS nvarchar(8))   Nov 23 2019, 05:14 PM
||- - stevep   QUOTE try: CAST(decimalDate AS nvarchar(8)) hope I...   Nov 23 2019, 05:22 PM
|||- - GroverParkGeorge   Yes, but my version of SSMS now treats it as a dat...   Nov 23 2019, 05:33 PM
|||- - GroverParkGeorge   Another option, one that I first thought of, is to...   Nov 23 2019, 05:38 PM
|||- - stevep   George thanks for your help so far, especially on ...   Nov 23 2019, 05:47 PM
|||- - GroverParkGeorge   Sometimes that break in the action serves as a mar...   Nov 23 2019, 05:54 PM
||- - MadPiet   just wondering, why would you use double-byte (NVA...   Nov 23 2019, 06:50 PM
||- - jleach   Here's my test: CODE-- int or string version ...   Nov 23 2019, 08:37 PM
||- - jleach   Forgot your "sometimes may be 0" CODE--...   Nov 23 2019, 08:39 PM
||- - CaptainMilly   This is completely random, but sometimes when I ha...   Dec 2 2019, 12:10 PM
|- - stevep   Should I just create a function for this and encap...   Nov 23 2019, 05:20 PM
- - AlbertKallal   I will say no matter how one slices and dices this...   Dec 2 2019, 03:20 PM
- - stevep   Oops I didn't check back in to see about my th...   Jan 9 2020, 06:47 PM
- - AlbertKallal   Thanks - I also think my example was/is easy on th...   Jan 18 2020, 07:43 PM
- - FrankRuperto   I don't think you can remove dbo because that...   Jan 18 2020, 08:58 PM



Custom Search


RSSSearch   Top   Lo-Fi    25th February 2020 - 10:28 AM