OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] (Unnecessary)Converting String to Date, I think

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
RedWolf3x
General User
General User


Joined: 17 Mar 2011
Posts: 18
Location: On the Verge of Breakdown

PostPosted: Wed Jun 22, 2011 8:16 am    Post subject: [SOLVED] (Unnecessary)Converting String to Date, I think Reply with quote

I've been working with some SQL code to verify if an employee is within a transition period.

I've created Query1:
Code:
SELECT TO_CHAR( CAST( CASE WHEN MONTH( "CSR Start Date" ) > 9 AND DAYOFMONTH( "CSR Start Date" ) >= 2 THEN YEAR( "CSR Start Date" ) + 1 WHEN MONTH( "CSR Start Date" ) <= 10 THEN YEAR( "CSR Start Date" ) ELSE YEAR( "CSR Start Date" ) + 1 END || '-' || CASE WHEN MONTH( "CSR Start Date" ) + 3 >= 13 AND DAYOFMONTH( "CSR Start Date" ) >= 2 THEN RIGHT( '0' || MONTH( "CSR Start Date" ) + 3 - 12, 2 ) WHEN MONTH( "CSR Start Date" ) + 3 < 13 AND DAYOFMONTH( "CSR Start Date" ) >= 2 THEN RIGHT( '0' || MONTH( "CSR Start Date" ) + 3, 2 ) WHEN MONTH( "CSR Start Date" ) + 2 >= 13 AND DAYOFMONTH( "CSR Start Date" ) = 1 THEN RIGHT( '0' || MONTH( "CSR Start Date" ) + 2 - 12, 2 ) WHEN MONTH( "CSR Start Date" ) + 2 < 13 AND DAYOFMONTH( "CSR Start Date" ) = 1 THEN RIGHT( '0' || MONTH( "CSR Start Date" ) + 2, 2 ) END || '-01' AS DATE ), 'MM/DD/YYYY' ) AS "Transition", "CSR List".* FROM "CSR List"


This Code works perfectly to return What I think is a string.

For testing purposes I've seperated my code, I'm trying to get

Query 2:
Code:
SELECT CASEWHEN( "Query1"."Transition" > CURRENT_DATE, 'True', 'False' ) AS "Probation", "Query1".* FROM "Query1"

To work, however it seems to not recognize the "Transition" as a date to compare to CURRENT_DATE. Is this because it is returning it as a string or for some other reason?

Is there a better way to complete this and if so how?

Any help is greatly appreciated.

Thanks
_________________
Red


______________________________________________
*The Color of Life*


Last edited by RedWolf3x on Thu Jun 23, 2011 7:17 am; edited 1 time in total
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Wed Jun 22, 2011 9:00 am    Post subject: Reply with quote

I don't remember if I have done this or not.....

Does it work if you hard code the date as either 'yyyy-mm-dd' (using single quotes) or with a time 'yyyy-mm-dd hh:mm:ss.SSSSSSSSS'
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jun 22, 2011 9:20 am    Post subject: Reply with quote

Asked and answered, please see below:

http://www.oooforum.org/forum/viewtopic.phtml?t=124380
Back to top
View user's profile Send private message
RedWolf3x
General User
General User


Joined: 17 Mar 2011
Posts: 18
Location: On the Verge of Breakdown

PostPosted: Wed Jun 22, 2011 9:51 am    Post subject: Reply with quote

Pitonyak,
I will definitely explore those possibilities, thanks.

Sliderule,
The thread you linked does what already works. I'm trying to do a secondary part, but thanks for the link.

I'm working on some of the logistics, but I think I may have come up with a work around. I'll post later what I did if it works out.
_________________
Red


______________________________________________
*The Color of Life*
Back to top
View user's profile Send private message
RedWolf3x
General User
General User


Joined: 17 Mar 2011
Posts: 18
Location: On the Verge of Breakdown

PostPosted: Thu Jun 23, 2011 7:16 am    Post subject: Reply with quote

Pitonyak,
Your idea worked flawlessly. Thank you so much.
_________________
Red


______________________________________________
*The Color of Life*
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Thu Jun 23, 2011 4:57 pm    Post subject: Reply with quote

Excellent
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group