February 1, 2013 at 6:14 pm
Is there a way to convert UniData datex to a sql date field in a query? :ermm:
February 2, 2013 at 2:22 am
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
February 4, 2013 at 11:07 am
If Dec 31, 1967 is the "border" date, and the date and time values really are separate integers::
For a date only:
SELECT
DATEADD(DAY, UniDataDays, CAST('19671231' AS date))
For a date and time:
SELECT
DATEADD(SECOND, UniDataSeconds, DATEADD(DAY, UniDataDays, '19671231'))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply