Converting dates between SQL Server and Postgres with Talend
skyman
I have a Talend job that runs a query on a SQL Server database and then writes selected data into a Postgres database. I am using the tMap component to do the mapping. The SQL Server dates are in similar to 2014-01-20 11:34:26.0 (configured as '"yyyy-MM-dd HH:mm:ss.SSS" in Talend) and the Postgres dates yyyy-MM-dd.
When I run the job I get the following exception:
[statistics] connected
Batch entry 0 INSERT INTO "criticalcareepisodes" ("GivenName","FamilyName","height","weight","mrn","id","admissiondate") VALUES ("Adam","Apple",100,200.0,123456,nextval('CriticalCareEpisodes_id_seq'),2009-11-10 21:30:00.000000 +11:00:00) was aborted. Call getNextException to see the cause.
[statistics] disconnected
Job ExtractSLICCriticalCareEpisode ended at 14:06 27/09/2014. [exit code=0]
어떤 이유로 든 날짜가 예상대로 형식화되지 않은 것 같습니다. 예외는 날짜 / 시간을 Postgres Date 유형으로 푸시하려는 것과 상당히 관련이 있습니다. 적어도 나는 그렇게 생각합니다. 나는 여기에서 분명한 것을 놓치고 있다고 확신하지만 어떤 도움을 주시면 감사하겠습니다.
도움이 될만한 이미지를 추가했습니다. 
ydaetskcoR
Ken Hampson의 답변 은 문제의 Postgres 부분을 다룹니다. 날짜 필드에 타임 스탬프를 삽입하려고합니다.
The problem is that you're trying to specify the intended format (yyyy-MM-dd) in the date pattern part of the Date type configuration in the schema. Talend actually holds the full date object at all times and the date pattern is merely for formatting purposes with Talend components such as a tLogRow that will then print the date in the format chosen.
To properly convert the date to a formatted string so you can send it to Postgres (which will implicitly convert the string into a date) you need to replace row1.CCDateAdmit with TalendDate.formatDate("yyyy-MM-dd",row1.CCDateAdmit).
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
에서 수정
- 이전 게시물：식의 형식 '()'을 'UINavigationController?'형식으로 변환 할 수 없습니다.
- 다음 포스트：httpd.conf에서이 코드를 정확히 어디에 사용합니까?
관련 기사
Related 관련 기사
- 1
differences between dates in sql
- 2
Converting Queries into SQL Server syntax
- 3
Converting Stored Procedure into a query (SQL Server Compact)?
- 4
SQL : SELECT date BETWEEN 오류 (postgres)
- 5
SQL Server 2008 R2 문제와 talend 연결
- 6
SQL Server의 Between 절
- 7
SQL to get number of working days between two dates
- 8
SQL SERVER 쿼리를 POSTGRES로 변경
- 9
Converting non-standard dates in r
- 10
Sql Server 2008: Conversion failed when converting date and/or time from character string
- 11
SQL Server Conversion error: Conversion failed when converting the nvarchar value 'XXX' to data type int
- 12
"Syntax error or access violation" when inserting dates using PDO and SQL Server
- 13
SQL Server 2008 two tables with common dates field how to select by datewise from both tables
- 14
SQL overlapping dates
- 15
Differences in distance calculated between javascript function and sql server STD distance
- 16
SQL Server의 Between 절이있는 Case 문
- 17
Password mismatch between SQL Server Management Studio and config file
- 18
Case when in where clause to choose between two columns ,SQL Server
- 19
Search between 2 dates in webmatrix
- 20
Difference in years between two dates
- 21
Starting Postgres Server
- 22
Prevent OpenOffice Calc from converting pasted numbers with decimals into dates
- 23
how to get a column with a list of dates between 2 known dates
- 24
How to display all the dates between multiple two dates in a table?
- 25
Insert into postgres SQL
- 26
Postgres Vacuumdb SQL
- 27
Postgres SQL 쿼리
- 28
Taking the Differences Between 2 Dates (R)
- 29
Calculating the days between two given dates
몇 마디 만하겠습니다