Convert cahr field to datetime

5 pts.
Tags:
SQL
I am trying to select data for last six months . The date field has a character data type. And it has the format like CYYMM(century,year,month) example: march2006 is '10603'. How can I select from that table the data for last six months. Please help.. Thanks in advance!

Answer Wiki

Thanks. We'll let you know when a new response is added.

what database? If you’re talking DB2, then you can use the following to get the date for 6 months ago :

substring(digits((year(now() – 6 months) * 100 + month(now()- 6 months)) – 190000),6,5)

or

substring( digits( ((year(now() – 6 months) * 10000 + month(now()- 6 months) * 100 + day(now() – 6 months)) – 9000000)
),4,7)

if you want to return days as well.

If you’re using SQL server, the convert function is easier to deal with.

If you’re using the above against a lot of columns, it may run slow. if it does, create a table in QTEMP, insert the result into the table, and then do a join to the table

Anyone know a better way to handle this? it’s the best I’ve been able to find for dealing with CYYMMDD type dates…

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ethacke1
    You may just want to use the 'between' keyword in SQL (criteria) for an easy solution: e.g., where (field) between '10512' and '10605' for the months of December 2005 - May of 2006 ('between' will, in effect, pull data that is >= the minimum value and
    0 pointsBadges:
    report
  • Tommie
    I had a similar situation. Simply convert your character variable to a dec character. Convert your Year/Month to a CYYMMDD format. Then convert to a date. I did this in a DB2 SQL stored procedure and called a sql UDF. DECLARE PERIOD CHAR ( 4 ); DECLARE YRMTH DEC ( 4 ) ; SET YRMTH = CAST ( PERIOD AS NUMERIC ( 4 , 0 ) ) ; SET MTHBEG = ( YRMTH * 100 ) + 1000001 ; CREATE FUNCTION PEGGYB.DEC2DATE ( DATEDEC DECIMAL(7, 0) ) RETURNS DATE LANGUAGE SQL . . . BEGIN /*This function converts a 7 digit date to a Date data type*/ /*ONLY WORKS FOR DATE >1939 &
    0 pointsBadges:
    report
  • TracyP
    You could also use the IN operator: SELECT (field list) FROM MyTable WHERE MyDateField IN ('10512','10601','10602','10603','10604','10605') Lots of solutions! Have fun! ~~~Tracy
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following