1 Reply by davec (edited by davec 28-09-2007 14:26:11)

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Vague dates and datetime types in sql server problems

Hi,

I'm trying to query our MSDE instance of Recorder 6. The VAGUE_DATE_START, VAGUE_DATE_END fields are stored as integers. I use 

CONVERT( CHAR(10), CAST( VAGUE_DATE_START AS DateTime), 103)

to give me a DD/MM/YYYY string for display and reporting. However during a batch run, my client code reports arithmetic overflow errors on converting data type datetime. In our SURVEY_EVENT table (for example), I have the following stats for the VAGUE_DATE_START field;

VAGUE_DATE_START values > 0 = 51279
VAGUE_DATE_START values < 0 =      30
VAGUE_DATE_START values = 0 =        4
VAGUE_DATE_START values null =      21
                                                  --------
                                                   51334

The figure of 51334 is correct for the total number of rows in that table. This is where it gets odd. If I manually test the CAST function, say

SELECT CAST( -1 AS DateTime)

I get 31/12/1899. So the function works with negative numbers and with zero and null fields. But if I run this;

SELECT CAST( VAGUE_DATE_START AS DateTime) 
FROM SURVEY_EVENT
WHERE VAGUE_DATE_START < 0

the query does not return a results set! However if I use NULL or > 0 or = 0, the query returns data, e.g. 

SELECT CAST( VAGUE_DATE_START AS DateTime)
FROM SURVEY_EVENT
WHERE VAGUE_DATE_START > 0

The queries are being run using SQL Manager 2005 lite.

Not having a lot of experience with SQL server, am I doing the right thing here? Are there workarounds to this problem? Does anyone know how to format the VAGUE_DATE_* fields into something human readable?

Any help gratefully received. Thanks.

Update
After posting this, I checked out the code on another sql manager and it reported the overflow for number less than zero. The other SQL manager must be silent in its
errors :rolleyes:

I looked for the largest negative numbers in the table and found these;

-513528, -412160, -295282

These do not look right, and cause the CAST function to fail. This is either bad data or I am using the wrong approach to this problem.

Dave.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

2 Reply by davec

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Vague dates and datetime types in sql server problems

Just a note to say that the max negative number which works in the CAST function is -53690 which equates to 01\01\1763 (SQL Servers cut off for past dates). This does suggest using CAST with vague dates is not perhaps not the right technique as it would mean that historic records past this date could not be held in Recorder. As a quick fix I can filter my code not to include numbers at that boundary, as we are reporting on more recent records anyway.

Of course the question remains as to how such large numbers get to be in the db. The max positive number in the SURVEY_EVENT table is 2958374 which if CAST becomes 03\02\9999! Like I say, I am not sure if vague dates are correct data for this function anyway. But if it is..boy have we got some weird data!

If anyone has time to reply, I would be keen to learn the correct procedures for formatting vague dates. Ta!

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

3 Reply by johnvanbreda (edited by johnvanbreda 01-10-2007 08:09:15)

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Vague dates and datetime types in sql server problems

There is some useful information on how vague dates are stored and how to format them on the Wiki at http://eim.metapath.org/wiki/index.php?title=Vague_Dates . I hope that will explain all!

Regards

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by davec (edited by davec 01-10-2007 08:35:03)

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Vague dates and datetime types in sql server problems

Wonderful! Thank you John. :D

The code example works fine.

I was having kittens here as I've noticed that the code examples I've been using return a date which is two days in front of that displayed in Recorder! I used the metadata button to get the survey_event_key and tested against the previous code. So, a simple CAST does not work but the DateAdd fuction does.

Just for reference if anyone has been looking in on this thread, the code I used to test my client against Recorder (as per the wiki link) was this;

SELECT 
     CONVERT(char(10), DATEADD(DAY, SURVEY_EVENT.VAGUE_DATE_START, '1899-12-30'), 103)
WHERE 
    SURVEY_EVENT_KEY = 'WA000059000011N6'

Kind Regards.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

5 Reply by davec

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Vague dates and datetime types in sql server problems

I just wanted to post a follow up on this thread.

Whilst the DATEADD function clearly works, there is a GOTCHA! to watch out for in simple queries. I noticed the count of rows returned from my client differed during a run using the SURVEY table. Recorder reports 234 surveys, whilst my client 231. When I looked at the "Allow records from" field, those three surveys had that date set to 1700.

This field equates to the FROM_VAGUE_DATE_START column in the SURVEY table. As the job of the client is to copy Recorder data over to a seperate reporting database (Postgresql), this creates a problem as I can not leave out top level surveys! Thankfully, the same technique can be used as discussed in the last posts. In postgresql this SQL returns an ISO date as per the example on the wiki;

SELECT DATE '1899-12-30' + 19387 AS DATE

Gives

DATE
------
'1953-01-28'

It also means I do not have to worry about the 1753 SQL Server cut off as something silly like this works;

SELECT DATE '1899-12-30' + -600000 AS DATE

produces

DATE
------
'0257-04-02'

Not that we are expecting records for the year 257! :)

Previously, using data from Recorder 2002, dates where stored in our reporting DB as timestamps. Although it means some re-coding and a change of the data types for the tables, I may as well just carry the vague dates across as integers and write a stored function to convert back to British form from within Postgres.

Regards.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

6 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Vague dates and datetime types in sql server problems

Here's a function Mike Weideli wrote for more robust and flexible integer-to-date conversions:

USE [NBNData]
GO
/****** Object:  UserDefinedFunction [dbo].[LCReturnDateShort]    Script Date: 10/02/2007 11:40:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[LCReturnDateShort]
(@VagueDate int, @VagueDateType varchar(2), @DatePart varchar(1)) RETURNS nvarchar(10)

/* Returns a date or date part for a given vague date field
    Author Michael Weideli - Littlefield Consultancy [http://www.lfield.co.uk] 14 October 2005
    F RETURNS FULL dd/mm/yyyy
    D RETURNS DAY
    Y RETURNS YEAR
    M RETURNS MONTH
    vague date type of U does not return anything

    EXAMPLE USAGE:
      LCReturnDateShort(VAGUE_DATE_START, VAGUE_DATE_END, 'F')
*/
AS
BEGIN
declare @MM1  nvarchar(9)
declare @MM2  nvarchar(9)
declare @RP1  nvarchar(10)
declare @Z    int
declare @G    int
declare @H    int
declare @A    int
declare @B    int
declare @Y    int
declare @C    int
declare @M    int
declare @D    int
declare @RD   int
declare @Y4   int
declare @RD1  int
declare @RM1  int
declare @RY1  int
declare @RDS1 nvarchar(2)
declare @RDS2 nvarchar(2)
declare @RD2  int
declare @RM2  int
declare @RY2  int
declare @RMS1 nvarchar(2)
declare @RMS2 nvarchar(2)
set @RD = @VagueDate + 693594
set @Z  = @RD + 306
set @H  = 100 * @Z - 25
set @A  = FLOOR(@H / 3652425)
set @B  = @A - FLOOR(@A / 4)
set @Y  = FLOOR((100 * @B + @H) / 36525)
set @C  = @B + @Z - 365 * @Y - FLOOR(@Y / 4)
set @M  = (5 * @C + 456) / 153
set @G  = (153 * @M - 457) / 5
set @D  = @C - @G
if @M >12 begin
  set @Y = @Y+1
  set @M = @M-12
end
set @RD1 = @D
set @RM1 = @M
set @RY1 = @Y
if @RD1 < 10 begin
  set @RDS1 = '0' + str(@RD1,1,0)
end
else begin
  set @RDS1 = str(@RD1,2,0)
end
if @RM1 < 10 begin
  set @RMS1 = '0' + str(@RM1,1,0)
end
else begin
  set @RMS1 = str(@RM1,2,0)
end
if @VagueDatetype <> 'U' begin
  set @RP1 = case @Datepart
    when 'D' then str(@RD1,2,0)
    when 'M' then str(@RM1,2,0)
    when 'Y' then str(@RY1,4,0)
    when 'F' then @RDS1 + '/'     + @RMS1 + '/' + str(@RY1,4,0)
  end
end

return @RP1

RETURN ''

END
Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

7 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Vague dates and datetime types in sql server problems

It's also worth reading Microsoft's comparison of CAST and CONVERT at http://msdn2.microsoft.com/en-us/library/ms187928.aspx for conversions of one data type to another.
This highly entertaining site also teaches us how to SET NUMERIC_ROUNDABORT

Time for bed.

Zebedee

8 Reply by davec

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Vague dates and datetime types in sql server problems

Thanks for all the code and links on this subject. I am copying across vague dates as integers now. Just for interest the Postgres version of Mike`s function is listed below. I hope that is ok Mike ;)

-- Function: "system".vague_date_part(vague_date_start integer, dtype character varying, part character varying)

-- DROP FUNCTION "system".vague_date_part(vague_date_start integer, dtype character varying, part character varying);

CREATE OR REPLACE FUNCTION "system".vague_date_part(vague_date_start integer, dtype character varying, part character varying)
  RETURNS character varying AS
$BODY$
/*
    Postgresql version of Mike Weideli's
    LCReturnDateShort function
*/
DECLARE
    iso_date    DATE;
    extract_str VARCHAR(5);
BEGIN
    iso_date := (DATE '1899-12-30' + vague_date_start)::DATE;
    
    -- PL/PGSQL does not has case selection.
    IF dtype <> 'U' THEN
        IF part = 'F' THEN
            RETURN TO_CHAR( iso_date, 'DD/MM/YYYY')::VARCHAR(10);
        ELSIF part = 'D' THEN
            extract_str := 'DAY';
        ELSIF part = 'M' THEN
            extract_str := 'MONTH';
        ELSIF part = 'Y' THEN
            extract_str := 'YEAR';
        END IF;
        RETURN DATE_PART(extract_str, iso_date)::VARCHAR(10);
    ELSE
        RETURN Null;
    END IF;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "system".vague_date_part(vague_date_start integer, dtype character varying, part character varying) OWNER TO dave;

Kind Regards,

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website