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 <> Stapleton
CIO DB|Wired.com / (ccs) Cowboy Computing Solutions
www.dbwired.com Web and Clarionet hosint
www.ccscowboy.com CCS SQL templates
www.Paywire.com ACH / Credit Card payment processing
"Suntower Systems" wrote in message
news:3d25d919@news.softvelocity.com...
> Converting a large app with LOTS of TPS dates (LONGs). Can someone
provide
> some guidance on how to convert these into SQL date fields?
>
> Can the TPS ---> SQL conversion program simply assign the data in TPS
format
> to the new SQL date field as below?
>
> SQLFile:DateField = TPSFile:DateField
> PUT(SQLFile)
>
> ---Thanks,
>
> ---JC
>
>
Printed May 4, 2024, 5:54 pm
This article has been viewed/printed 35142 times.
Google search
has resulted in 1009 hits on this article since January 25, 2004.