#SQLServer Working with Dexterity Dates and Times in Transact-SQL


David Meego - Click for blog homepageWhen Dexterity was first implemented on SQL Server for version 3.15, there was a number of decisions made on how to map the datatypes, fields and tables designed for ISAM platforms such as Ctree and Btrieve/Pervasive SQL to work on SQL Server.

One of the decisions was how to map the individual date and time control types to work with the datetime datatype in SQL Server.

The purpose of this article is to create a quick reference to find how to write a SQL datetime into a Dexterity table which is expecting a date or time, but not a datetime.

For a Dexterity date field to be stored in a SQL datetime column, it is stored in the date part with the time part set to midnight or ’00:00:00′. Below is the Transact-SQL commands to cast a SQL datetime to a Dexterity date:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 112)) AS [DATE]

For a Dexterity time field to be stored in a SQL datetime column, it is stored in the time part with the date part set to ‘1900-01-01’. Below is the Transact-SQL commands to cast a SQL datetime to a Dexterity time:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(8), GETDATE(), 108)) AS [TIME]

Hope this information is useful.

David

This article was originally posted on http://www.winthropdc.com/blog.

6 thoughts on “#SQLServer Working with Dexterity Dates and Times in Transact-SQL

  1. One problem that has puzzled me is how to map a Dexterity text field to a field in a SQL stored procedure, I get a Dexterity compile error when I try to match a Dexterity text field to a varchar(MAX) field in a SQL SP.

    Code like the following, which splits up 30,000 characters into 120 nvarchar(250) fields, will work, but it is not very elegant.

    Do you know of a more elegant solution?

    dex code snippet, calling SQL stored procedure
    for j = 1 to 120 do
    strBody[j] = substring(txtBody,1+(250*(j-1)),250);
    if empty(trim(strBody[j])) then
    clear field strBody[j];
    exit for;
    end if;
    end for;
    call YukkyStoredProcedure,longSprocStatus,
    strBody[1],strBody[2],strBody[3],strBody[4],strBody[5],strBody[6],strBody[7],etc. up to 120
    ( the stored procedure would list the parameters as strBody001,strBody002,etc )

    Like

  2. Hi Bruce

    Not sure if this helps, but I needed to pass a text field bigger than 32768 characters to SQL when creating some complex stored procedures from inside Dexterity.

    I ended up using a temporary table with two fields (Sequence Number and 255 character string) to create the lines and then used SQL to read the table contents and build that into the extra large varchar which it could then execute to create the stored procedure.

    David

    Like

  3. Speaking of date fields…….

    Is there any way to turn off the calendar date picker on a date field? I was sort of hoping for a dex.ini switch or something that prevents that little calendar icon appearing. I have been googling for information but I haven’t found anything as yet.

    Like

      • Bugger!! The problem is that the icon takes up the last 2 characters of the date fields, chopping of the year. This means I have to alter the size of every date field on every screen. It will take me days.

        Oh well, I thought it was worth asking as you would probably know better than anyone else.

        Mark

        Like

      • Hi Mark

        The Date Picker has been part of Dexterity for many versions. There was a converter program when it was introduced (maybe v6).

        If there was a Dex.ini setting it would affect the entire system which is not fair to other products and users.

        You can export forms and do a text find and replace (as long as you work out your terms correctly), then import them again. BACK UP FIRST.

        David

        Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.