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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 97
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
 
June7
post Nov 23 2019, 04:17 PM
Post#2



Posts: 1,009
Joined: 25-January 16



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

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 04:45 PM
Post#3


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


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



--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 04:50 PM
Post#4


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
stevep
post Nov 23 2019, 05:04 PM
Post#5



Posts: 97
Joined: 9-November 14



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
Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 05:06 PM
Post#6


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 05:14 PM
Post#7


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


try: CAST(decimalDate AS nvarchar(8))

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
stevep
post Nov 23 2019, 05:20 PM
Post#8



Posts: 97
Joined: 9-November 14



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



Go to the top of the page
 
stevep
post Nov 23 2019, 05:22 PM
Post#9



Posts: 97
Joined: 9-November 14



QUOTE
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.
This post has been edited by stevep: Nov 23 2019, 05:26 PM
Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 05:33 PM
Post#10


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


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

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



--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 05:38 PM
Post#11


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


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

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

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
stevep
post Nov 23 2019, 05:47 PM
Post#12



Posts: 97
Joined: 9-November 14



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.

Go to the top of the page
 
GroverParkGeorge
post Nov 23 2019, 05:54 PM
Post#13


UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Nov 23 2019, 06:50 PM
Post#14



Posts: 3,364
Joined: 27-February 09



just wondering, why would you use double-byte (NVARCHAR) instead of regular VARCHAR?
Go to the top of the page
 
jleach
post Nov 23 2019, 08:37 PM
Post#15


UtterAccess Administrator
Posts: 10,274
Joined: 7-December 09
From: St. Augustine, FL


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

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
jleach
post Nov 23 2019, 08:39 PM
Post#16


UtterAccess Administrator
Posts: 10,274
Joined: 7-December 09
From: St. Augustine, FL


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

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
CaptainMilly
post Dec 2 2019, 12:10 PM
Post#17



Posts: 153
Joined: 13-August 13



This is completely random, but sometimes when I have an issue with data conversion I export to csv and reimport and it gets fixed.
Go to the top of the page
 
AlbertKallal
post Dec 2 2019, 03:20 PM
Post#18


UtterAccess VIP
Posts: 2,902
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
 


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 09:26 PM