| View previous topic :: View next topic |
| Author |
Message |
RedWolf3x
General User
Joined: 17 Mar 2011
Posts: 18
Location: On the Verge of Breakdown
|
 Posted: Wed Jun 22, 2011 8:16 am Post subject: [SOLVED] (Unnecessary)Converting String to Date, I think |
 |
|
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 |
|
 |
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3623
Location: Columbus, Ohio, USA
|
| Posted: Wed Jun 22, 2011 9:00 am Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
|
| Back to top |
|
|
RedWolf3x
General User
Joined: 17 Mar 2011
Posts: 18
Location: On the Verge of Breakdown
|
| Posted: Wed Jun 22, 2011 9:51 am Post subject: |
|
|
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 |
|
|
RedWolf3x
General User
Joined: 17 Mar 2011
Posts: 18
Location: On the Verge of Breakdown
|
| Posted: Thu Jun 23, 2011 7:16 am Post subject: |
|
|
Pitonyak,
Your idea worked flawlessly. Thank you so much.
_________________
Red
______________________________________________
*The Color of Life* |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3623
Location: Columbus, Ohio, USA
|
|
| Back to top |
|
|
|
