Thread: how do i create a date from a substring???
greetings all! [i just became a member] i have a string data from a view that is a packed field. it contains a date, a time and a user's initials. i'm trying to extract the date portion in a pg 7 view. the data originally came from a ms sql 7 table that has since been converted into a pg 7 table. in the ms sql 7 view the date was extracted as follows: convert(timestamp, substring(creat, 1, 8), 120) = 'createdate' where creat is the packed field from the original table and 'createdate' is the extracted date portion. the data would typically look like: 200111171623XYX. the result is '2001-11-17'. i've reviewed documentation, on line books and several threads in this and related newsgroups and can't seem to come up with a decent solution. i've tried various combinations of this in a pg 7 view: to_date(substr(creat,1,8),'YYYY-MM-DD') = 'createdate' to_date(substring(creat from 1 for 8),'YYYY-MM-DD') = 'createdate' to_timestamp(substr(creat,1,8),'YYYY-MM-DD') = 'createdate' to_timestamp(substrsting(creat from 1 for 8),'YYYY-MM-DD')= 'createdate' and continually get different errors: "bad date external representation 'createdate'" or "bad timestamp external representation 'createdate'" i'm sure this has an obvious solution but i can't seem to find it. any suggestions are appreciated. regards, yusuf __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
joe.guyot wrote: > greetings all! > > [i just became a member] > > i have a string data from a view that is a packed field. it > contains a date, a time and a user's initials. i'm trying to extract > the date portion in a pg 7 view. the data originally came from a ms > sql 7 table that has since been converted into a pg 7 table. > > in the ms sql 7 view the date was extracted as follows: > > convert(timestamp, substring(creat, 1, 8), 120) = 'createdate' > > where creat is the packed field from the original table and > 'createdate' is the extracted date portion. the data would typically > look like: 200111171623XYX. the result is '2001-11-17'. > > i've reviewed documentation, on line books and several threads in > this and related newsgroups and can't seem to come up with a decent > solution. > > i've tried various combinations of this in a pg 7 view: > > to_date(substr(creat,1,8),'YYYY-MM-DD') = 'createdate' > to_date(substring(creat from 1 for 8),'YYYY-MM-DD') = > 'createdate' > to_timestamp(substr(creat,1,8),'YYYY-MM-DD') = 'createdate' > to_timestamp(substrsting(creat from 1 for 8),'YYYY-MM-DD') = > 'createdate' > > and continually get different errors: > "bad date external representation 'createdate'" > or > "bad timestamp external representation 'createdate'" Your substring-date isn't in 'YYYY-MM-DD' format, but in 'YYYYMMDD' Regards, Tomasz Myrta