Обсуждение: how do i create a date from a substring???

Поиск
Список
Период
Сортировка

how do i create a date from a substring???

От
"joe.guyot"
Дата:
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

Re: how do i create a date from a substring???

От
Tomasz Myrta
Дата:
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