postgre 9.5 import to MSSQL Server 2012 r2 verification issue

I have a postgresql table with 8 million rows. I need to copy it over to MSSQL. I do not have access to the console so I am unable to use PG_Dump. I have the ODBC driver, and have successfully linked up the servers. I was then able to copy the table over month by month. This was slow and painstaking. I need to create a query in postgresql that will list the year, month and total rows per month. The date field was actually created as text. I have tried this but do not get the results I am looking for.

SELECT distinct date_part('Year',case WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD') else to_date(trandate, 'MM/DD/YYYY') end) as Year ,date_part('Month',case WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD') else to_date(trandate, 'MM/DD/YYYY') end) as month ,count(date_part('year',case WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD') else to_date(trandate, 'MM/DD/YYYY') end)) as "trandate sum"--date_part('Day',case -- WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD') -- else -- to_date(trandate, 'MM/DD/YYYY') -- end)) FROM SomeSchema.transaction_detail

Group By trandate order by date_part('Year',case WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD') else to_date(trandate, 'MM/DD/YYYY') end) ,date_trunc('Mon' date_part('Month',case WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD') else to_date(trandate, 'MM/DD/YYYY') end))

Is there a better way to do this?

Replay

Category: postgresql Time: 2016-07-28 Views: 0
Tags: postgresql

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.180 (s). 12 q(s)