StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POnormalize date in postgres - character to date
primarykey
Id
20450942
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-12-08T07:28:37.630
FavoriteCount
0
LastActivityDate
2013-12-08T10:30:10.097
LastEditDate
2013-12-08T10:17:12.637
LastEditorUserId
2750968
OwnerUserId
2750968
ParentId
0
PostTypeId
1
Score
2
ViewCount
2216
LastEditorDisplayName
text
Body
I have a Table with coloumnname <code>description</code> <pre><code>description //character varying LZ000834_28-02-14 LZ000834_28-02-14 LA20683_30-04-15 LA20683_30-04-15 LA20300_31-01-15 LA20300_31-01-2015 LA20264_31-01-15 LA20264_31-01-2016 LAN2078_31-03-16 LAN2078_31-03-15 LAN8394_31-04-14 L2Z82736_31_03_2015 //has 1million rows </code></pre> here description means batchname_expirydate my question is how do I normalize my description column convert all those dates to <code>DD-MM-YY</code> format i have tried this two queries <pre><code>select substring(description from position('_' in description) +1) from attributesetinstance; </code></pre> above query will give me all the strings then i tried date conversion like this <pre><code>select to_date(substring(description from position('_' in description) +1), 'DD-MM-YY') from attributesetinstance; </code></pre> now this gives me error <pre><code>ERROR: invalid value "_3" for "DD" DETAIL: Value must be an integer. ********** Error ********** ERROR: invalid value "_3" for "DD" SQL state: 22007 Detail: Value must be an integer. </code></pre> how do update/recorrect all my database ? update: tried with another sql <pre><code>with product_dates AS ( select description, ((val[2])||'-'||val[3]||'-'||val[4])::date as expir_date from ( select description,regexp_matches(description, '([a-zA-Z0-9]+)_([0-9]+)[-_]([0-9]+)[-_]([0-9]+)') as val from attributesetinstance ) a ), expiring_dates AS ( select description from product_dates ) select description from expiring_dates </code></pre> i get following error: <pre><code>ERROR: date/time field value out of range: "31-04-14" ********** Error ********** ERROR: date/time field value out of range: "31-04-14" SQL state: 22008 </code></pre> update my postgres datestyle <pre><code>show datestyle; "ISO, DMY" </code></pre>
Tags
<postgresql><date><substring><trim><to-date>
Title
normalize date in postgres - character to date
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USMani Deep
UserOwnerUserId
1. USMani Deep
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POnormalize date in postgres - character to date
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.