UniData stores a date as a single integer with 0 representing December 31, 1967,

and 1 representing January 1, 1968, and so on. Negative numbers represent dates

before December 31, 1967. UniData stores time as an integer between 0 and 86400

(seconds since midnight).

The best approach is to create a date table. In this case I've chosen to make it a permanent table.

CREATE TABLE [dbo].[UniDataDates](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[SQLDate] [datetime2](7) NOT NULL,

[UniDataRaw] [bigint] NOT NULL,

CONSTRAINT [PK_UniDataDates] PRIMARY KEY CLUSTERED

(

[ID] ASC,

[SQLDate] ASC,

[UniDataRaw] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Next, I populate the table.

;WITH cteDates AS

(

SELECT

DATEADD(day,0,0) AS SQLDate

,CAST(-24835 AS BIGINT) AS UniDataDate

UNION ALL

SELECT

DATEADD(day,1,cteDates.SQLDate) AS SQLDate

,CAST(cteDates.UniDataDate + 1 AS BIGINT) AS UniDataDate

FROM

cteDates

WHERE

UniDataDate < 29951

)

INSERT INTO dbo.UniDataDates

SELECT

SQLDate

,UniDataDate

FROM

cteDates

OPTION (MAXRECURSION 0)

Now I can write a function to do the conversion. I chose an iTVF so we could use it in a cross-apply later on. I wrote the function so it will do conversions in both directions.

CREATE FUNCTION dbo.ConvertUniDataDate

(

@UniDataDate VARCHAR(20)

,@InputDate DATETIME

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(

SELECT

(SELECT

DATEADD(second,CAST(RIGHT(@UniDataDate,CHARINDEX('.',REVERSE(@UniDataDate)) - 1) AS BIGINT)

,(SELECT SQLDate FROM dbo.UniDataDates

WHERE ID > 0 AND UniDataRaw = CAST(LEFT(@UniDataDate,CHARINDEX('.',@UniDataDate) - 1) AS BIGINT)))

) AS SQLDate

,(SELECT CAST(

(SELECT CAST(UniDataRaw AS VARCHAR(10)) FROM dbo.UniDataDates WHERE ID > 0 AND SQLDate = CAST(@InputDate AS DATE))

+'.'+(SELECT CAST(DATEPART(minute,@InputDate)*60+DATEPART(second,@InputDate) AS VARCHAR(10)))

AS VARCHAR(20))

) AS UniDataRaw

)

GO

The basic method for calling the function:

SELECT SQLDate FROM dbo.ConvertUniDataDate('100.25000',NULL)

SELECT UniDataRaw FROM dbo.ConvertUniDataDate(NULL,'1968-04-09 00:41:40.000')

A more complex example pulling data from a table and doing a CROSS APPLY with the iTVF.

This example proves that the function works in both directions.

IF OBJECT_ID('tempdb..#UDTable') IS NOT NULL

DROP TABLE #UDTable

CREATE TABLE #UDTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[UniDataRaw] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

INSERT INTO #UDTable

SELECT '100.2500'

UNION

SELECT '16101.15000'

UNION

SELECT '17490.34567'

UNION

SELECT '0.0'

UNION

SELECT '18111.56789'

SELECT

dt.UniDataRaw

,ud.SQLDate

FROM

#UDTable AS dt

CROSS APPLY

dbo.ConvertUniDataDate(dt.UniDataRaw,NULL) AS ud

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL

DROP TABLE #DateTable

CREATE TABLE #DateTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[SQLDate] DATETIME NULL,

PRIMARY KEY (ID))

INSERT INTO #DateTable

SELECT '1967-12-31 00:00:00.000'

UNION

SELECT '1968-04-09 00:41:40.000'

UNION

SELECT '2012-01-30 04:10:00.000'

UNION

SELECT '2015-11-19 09:36:07.000'

UNION

SELECT '2017-08-01 15:46:29.000'

SELECT

ud.UniDataRaw

,dt.SQLDate

FROM

#DateTable AS dt

CROSS APPLY

dbo.ConvertUniDataDate(NULL,dt.SQLDate) AS ud

Both of these queries return the identical results:

UniDataRawSQLDate

0.01967-12-31 00:00:00.000

100.25001968-04-09 00:41:40.000

16101.150002012-01-30 04:10:00.000

17490.345672015-11-19 09:36:07.000

18111.567892017-08-01 15:46:29.000