Convert string to datetime - Performance

Dear Friends,

I have a doubt relation when converting a field…

I ‘m converting a string to datetime inside a SQL command in an OLE DB Source using this:

convert(datetime,[Maturity],103) As MaturityDate

It’s better to use inside the OLE DB Source or is better to convert it with data conversion transformation?

Regards!!

Thanks

Probably just a matter of opinion. SSIS can do datatype conversions, but has to create a new column in the pipeline to hold the new type, which is a little bit inefficient. I generally don't like things "hidden" in the OLE DB Source, but in this case I think that's what I'd do.
|||

But witch of these options is more fast?!

Regards!

|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

I think SQL would be faster. And because I hate being wrong, I tested it before I said that. I averaged the times for three runs of 20,000 rows and SQL appears 12% faster than SSIS.
|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

Why don't you try it and tell us. You're in the best position to judge because its your data.

-Jamie

|||

I'm agree with you jamie!! :-)

What a question I made... eheheh... I can see the time in the logs of the cube!!!

Sorry...

Tomorrow I will give you the response!! thanks both!!

|||Someone help me?!|||

PedroCGD wrote:

Someone help me?!

Umm, with what? Call 911? |||

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

|||

PedroCGD wrote:

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

Good to knw (and as expected). Thanks Pedro.

Convert report in PDF Format

Hi,

I have three differents sub-reports, each one inside a table object.

Each table has a property "page break-before" enable.

When I execute the view-report in HTML 4.0 format, the reports are executed with success, but when I export to PDF format, the property "page break-before" inserts a blank page at the end of each sub-report. How can I do to suppress the blank-pages when exporting to PDF format ?

Hi Nitini-

Without the .rdl I can't be sure, but here are few things to check. In general be aware that the behavior for defining pages is different between HTML and PDF. PDF has a strict restriction on where page breaks are defined, while HTML can be broken (essentially) at any point.

(1) The page break is defined at the table vs. the table group level.
(2) Blank space - often times unintentionally having blank space can lead to extra pages (as blacnk space is preserved).
(3) Note that page breaks in subreports are not used.
(4) Overlapping items, HTML will move items that are overlapping, PDF will not.

Hope that helps,
-JonHP

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

Convert Oracle "connect by level < 10" into MS SQL server 2005

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

We have recursion in SQL Server, but are you asking about the CONNECT BY LEVEL auto-numbering use?

Buck Woody

|||

If you're looking for the hierarchical query in SQL Server, you can check here:

http://www.databasejournal.com/features/oracle/article.php/3552521

Buck Woody

|||MSDN says there is no SQL equivalent to CONNECT BY:
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/admincmp/75517c11.mspx?mfr=true

It suggests using a stored procedure instead.
|||That reference is for the SQL 7 docs (almost 10 years old). IIRC Connect by with level is recursion with an automatic recursion depth checker/autonumber. Start with Bucks link.

Convert date and time to string?

Hi,

Can anyone tell me how to convert date and time to string.

I got one field inside my database which contain data like below:

4/16/2004 10:19:01 AM

if i wan to call out a record which have to refer to the data above

Select *
From table_A
where field_date= ???

what to fill in the ??

ThanxMan, you were so close! Use:Select *
FROM table_A
WHERE field_date = '4/16/2004 10:19:01 AM'-PatP|||Originally posted by Pat Phelan
Man, you were so close! Use:Select *
FROM table_A
WHERE field_date = '4/16/2004 10:19:01 AM'-PatP

hi there again....

I tired like what you hav told me but i cant get any output from the query statement lo.its like the record doesn't exist.

its like the format i keyed in is not the same.

Can u pls explain .
thanx|||My guess would be that you are missing the milliseconds. The value in the database probably has milliseconds, but your constant does not.

Databases are finicky about things like that! ;)

-PatP