Pervasive - how to Cast date String Fields to Date types?

tseyfarth · **Joined:** Sat Dec 04, 2010 9:14 pm **Posts:** 918

Hello all,

I need to do searching using fields that hold dates (and times) - in a string format. Can anyone tell me the SQL code to use that will case these properly?
Basically these fields are used for sorting and searching.

My current code is this:

select * from "MGMNT_UPDATE" where "MGMNT_UPDATE"."Date" >= '3/20/2013' and "MGMNT_UPDATE"."Date" <= '3/20/2013' order by  "MGMNT_UPDATE"."Date", "MGMNT_UPDATE"."Time" DESC;

But I do not know how to change this to make the SQL engine understand it is a date that I am after.

Ideas anyone?

Thank you,
Tim

timhare · **Posted:** Thu Mar 21, 2013 2:20 am

SQL doesn't care. Everything is a string in a select statement. You just have to format the string properly, which I thought we had done in a previous post. Something like

where "MGMNT_UPDATE"."Date" >= {d '2013-03-20'}

tseyfarth · **Joined:** Sat Dec 04, 2010 9:14 pm **Posts:** 918

Hi Tim,

The difference is, now instead of the from/to date being in question, the date field itself is a string type instead of a date type.

Previous like this: where "MGMNT_UPDATE"."Date" >= {d '2013-03-20'}
Tried like this: where "MGMNT_UPDATE".{d ' "Date" '} >= {d '2013-03-20'}
And This where "MGMNT_UPDATE"."{d'Date'}" >= {d '2013-03-20'}

I could not get it to work - hence the question.

Tim

timhare · **Posted:** Thu Mar 21, 2013 1:14 pm

How is the data in the field formatted? Best case it would be standard ansi date format YYYY-MM-DD. Then you can just use SQLDate. Otherwise, you would have to use whatever function Pervasive supplies to do the conversion. DateValMask perhaps?

tseyfarth · **Joined:** Sat Dec 04, 2010 9:14 pm **Posts:** 918

Hi Tim,
MM/DD/YYYY

I tried also using Convert:
select * from "MGMNT_UPDATE" where CONVERT("MGMNT_UPDATE"."Date",'yyyy-mm-dd') >= '3/21/2012' and CONVERT("MGMNT_UPDATE"."Date",'yyyy-mm-dd') <= '3/21/2012';

I get "predicate" errors on yyyy...

timhare · **Posted:** Thu Mar 21, 2013 6:23 pm

CAST might work for you.

where Cast("MGMNT_UPDATE"."Date", datetime) >= '2012-03-21'

pfargo · **Joined:** Mon Oct 13, 2008 4:26 am **Posts:** 63

From Pervasive docs. This may get what you need.

The following example converts a string to SQL_DATE then adds 31 to SQL_DATE.
SELECT Name FROM Class WHERE Start_date > CONVERT ('1995-05-07', SQL_DATE) + 31

tseyfarth · **Joined:** Sat Dec 04, 2010 9:14 pm **Posts:** 918

Thanks guys,

Will try casting later.
The example in the PSQL docs are for a string - but not a column that is a string. PSQL is very particular in how columns are handed to it, unfortunately.

Tim

shusseina · **Joined:** Fri Nov 16, 2012 11:58 pm **Posts:** 6

If you querying a SQL Server database CASTing the columns to datetime values should work. If MGMNT_UPDATE.Date holds a string value then you probably want to indicate that in the column name to avoid confusion, e.g. MGMNT_UPDATE.DATE_STRING.

select * from "MGMNT_UPDATE" where CAST("MGMNT_UPDATE"."Date" AS DATETIME) >= '3/20/2013' and CAST("MGMNT_UPDATE"."Date" AS DATETIME) <= '3/20/2013' order by  "MGMNT_UPDATE"."Date", "MGMNT_UPDATE"."Time" DESC;

I can't test the above, so if it does work, try CASTing the date literals.

select * from "MGMNT_UPDATE" where CAST("MGMNT_UPDATE"."Date" AS DATETIME) >= CAST('3/20/2013' AS DATETIME) and CAST("MGMNT_UPDATE"."Date" AS DATETIME) <= CAST('3/20/2013' AS DATETIME) order by  "MGMNT_UPDATE"."Date", "MGMNT_UPDATE"."Time" DESC;

By the way, I suggest you use aliases in your SQL to reduce the length of the statements (if only for the sake of your fingers). By using underscores in your table and columns names (and any other DB objects) rather than spaces, you can avoid needing to type double quotes everywhere.

select *
  from MGMNT_UPDATE A
  where CAST(A.Date AS DATETIME) >= '2000-03-20'
  and CAST(A.Date AS DATETIME) <= '3/20/2013'
  order by A.Date, A.Time DESC;

Next, you might want to uppercase only SQL keywords and leave everything else lowercase.

SELECT *
  FROM mgmnt_update a
  WHERE CAST(a.date AS DATETIME) >= '2000-03-20'
  AND CAST(a.date AS DATETIME) <= '3/20/2013'
  ORDER BY a.date, a.time DESC;

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

Pervasive - how to Cast date String Fields to Date types?