Convert from MSSQL DATETIME to Claion DATE in MSSQL

1

In my clarion program, I have some tables which uses Clarion Standard Date as dates, and others uses datetime on the backend.

Now i need to make an MSSQL query, which compares two dates, one in datetime and one in clarion date.
How to convert from datetime to clarion?

Best regards

Edvard Korsbæk

2

If using this directly in a select statement then replace the @Variables with the correct column from the table.

/* Clarion Date to SQL Date */
DATEADD(DAY,@ClarionDate,‘1800-12-28’)

/* SQL DateTime to Clarion Date */
DATEDIFF(DAY,‘1800-12-28’, CAST(@SQLDate AS DATE))

/* Convert Clarion TIME to SQL time*/
convert(time, dateadd(ms, (@ClarionDate-1)*10,0))

3

Answered in c11 SV newsgroup, but for completeness

SELECT …
FROM …
WHERE ClarionDate=CAST(SQLDateTimeWith0TimePart AS INT)+36163

Or

WHERE ClarionDate=CAST(CAST(SQLDateTime AS FLOAT) AS INT)+36163

As from DATETIME to INT it rounds, but from FLOAT to INT it truncates
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#truncating-and-rounding-results

It behaves as if it was:
WHERE ClarionDate=CAST(FLOOR(CAST(SQLDateTime AS FLOAT)) AS INT)+36163

If you have a DATE field you can convert it to DATETIME first then to INT
WHERE ClarionDate=CAST(CAST(SQLDate AS DATETIME) AS INT)+36163

4

Here are funtions we have in MSSQL that may be useful:

CREATE function [dbo].[ClarionDate] (@pSQLDate datetime)
returns int
as
begin
select @pSQLDate = cast (substring (convert (varchar, @pSQLDate, 120), 1, 10) as datetime)
return cast (@pSQLDate - cast (‘1800-12-28’ as datetime) as int)
end

CREATE function [dbo].[SQLDate] (@pClarionDate int)
returns datetime
as
begin
return (cast (‘1800-12-28’ as datetime)+@pClarionDate)
end

5

I’ve always represented these clariondate like this in a select statement:

‘<39>’ & FORMAT(clariondate,@d10-) & ‘<39>’

Joe