Login
Templates, Tools and Utilities
for Clarion Developers
Templates, Tools and Utilities
|
||
|
|
|
Icetips Article
Back to article list
Search Articles
Add Comment
Printer friendly
Direct link
SQL Related Articles: Clarion dates to SQL 2002-07-05 -- Andy Stapleton Newsgroups: softvelocity.products.c55ee
Dump the file to the table with the Long format numbers....
depending on the version of DB, I can give you a stored Function to convert
it to a valid SQL date, then you can do like the following.
insert into Mytable(sysid,Name,Entrydate)
select Sysid,Name,(Select SF_Validdate(DateField))
from ConvertTable
then the entire thing can be done at once...
The Function is simple....
Find out the Min Date from the DB your using...
SET DBMinDate = '1753/01/01' ! MSSQL
SET DBMinDate = '0001/01/01' !Sybase
SET CWMinDate = '12/28/1800'
SET AddDays = DATEDIFF(DAY,MinDBDate,CWMINDate)
Set TotalDays = CWLongDate+AddDays
Set ReturnDate = DATEADD(Day,TotalDays,DBMinDate)
Return date should be a DATE field or DATETIME.
to do this as a single function in a select statement
Set the SQLDate = 73603 ! todays date in long format.
Select
DateADD(Day,((DateDiff(Day,'0001/01/01','1800/12/28')+SQLDATE)),'0001/01/01'
)
This returns '2002-07-05' which is correct...
So I could....
Select
Sysid,CustomerInvoice,DateADD(Day,((DateDiff(Day,'0001/01/01','1800/12/28')+
TransDate)),'0001/01/01')
from Invoice
and should return the systemid, invoice number and the valid SQL date for
the transaction.
HTH
--
Andy <
Today is November 21, 2024, 3:49 am This article has been viewed 35326 times. Google search has resulted in 1009 hits on this article since January 25, 2004.
|
|