Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Mon Jul 24, 2017 11:34 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: Pervasive - how to Cast date String Fields to Date types?
PostPosted: Thu Mar 21, 2013 12:29 am 
Offline

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Thu Mar 21, 2013 2:20 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
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'}


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Thu Mar 21, 2013 1:08 pm 
Offline

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Thu Mar 21, 2013 1:14 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
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?


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Thu Mar 21, 2013 1:51 pm 
Offline

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Thu Mar 21, 2013 6:23 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
CAST might work for you.

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Fri Mar 22, 2013 5:24 am 
Offline

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Fri Mar 22, 2013 12:32 pm 
Offline

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


Top
 Profile  
Reply with quote  
 Post subject: Re: Pervasive - how to Cast date String Fields to Date types
PostPosted: Mon May 13, 2013 6:16 am 
Offline

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;


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group