Real Software Forums
http://forums.realsoftware.com/

Pervasive - how to Cast date String Fields to Date types?
http://forums.realsoftware.com/viewtopic.php?f=3&t=47358
Page 1 of 1

Author:  tseyfarth [ Thu Mar 21, 2013 12:29 am ]
Post subject:  Pervasive - how to Cast date String Fields to Date types?

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

Author:  timhare [ Thu Mar 21, 2013 2:20 am ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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'}

Author:  tseyfarth [ Thu Mar 21, 2013 1:08 pm ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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

Author:  timhare [ Thu Mar 21, 2013 1:14 pm ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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?

Author:  tseyfarth [ Thu Mar 21, 2013 1:51 pm ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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...

Author:  timhare [ Thu Mar 21, 2013 6:23 pm ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

CAST might work for you.

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

Author:  pfargo [ Fri Mar 22, 2013 5:24 am ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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

Author:  tseyfarth [ Fri Mar 22, 2013 12:32 pm ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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

Author:  shusseina [ Mon May 13, 2013 6:16 am ]
Post subject:  Re: Pervasive - how to Cast date String Fields to Date types

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;

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/