Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Not able to find dateTime value using sql

by bimleshsharma (Beadle)
on Nov 14, 2012 at 12:17 UTC ( #1003790=perlquestion: print w/ replies, xml ) Need Help??
bimleshsharma has asked for the wisdom of the Perl Monks concerning the following question:

i am using sql statement to get value of datetime datatype field but it is giving only date instead of complete value. value in table is like

trade_time 11/9/2012 3:19:18.000 PM 11/9/2012 6:33:06.000 PM 11/9/2012 3:19:18.000 PM
but output coming only:
11/9/2012
Here is code i am using.

$original_tradeTime= qx!kfsql -S SERVER -D big -F e -s , -c \"select t +op 1 *,convert(datetime,trade_time) from trx where trade_id = 123\"!; chomp $original_tradeTime; $original_tradeTime =~ s/\"//g; print "\noriginal_tradeTime=$original_tradeTime";

Comment on Not able to find dateTime value using sql
Select or Download Code
Re: Not able to find dateTime value using sql
by space_monk (Chaplain) on Nov 14, 2012 at 12:49 UTC

    You really should be using some of the DB libraries instead of executing a shell command to access the database.

    Are you sure the command is actually returning a full datetime? After all, chomp only removes the ending record separator (line feeds) and the s// statement only removes quote marks, leading me to believe the output of your SQL is incorrect.

    A Monk aims to give answers to those who have none, and to learn from those who know more.
      Yes, SQL output is not correct if running with code. While same sql query is returning fine at sql command prompt.
Re: Not able to find dateTime value using sql
by space_monk (Chaplain) on Nov 14, 2012 at 12:54 UTC
    As far as I can tell, trade_time is a datetime record, so what is the convert function doing? Try removing the convert function.
    A Monk aims to give answers to those who have none, and to learn from those who know more.
Re: Not able to find dateTime value using sql
by marto (Chancellor) on Nov 14, 2012 at 13:10 UTC

    Debugging tips:

    • Run your SQL against the database, removing Perl (or whatever) from the equation. If the result isn't what you want, your SQL is most likely wrong, less likely is that there's a bug in the database product(s) you use.
    • If the SQL works as expected then examine how you manipulate the results afterwards. Basic print statements, before and after manipulation will help.
    • Debugging and Optimization from tutorials

    Also, consider using DBI to interact with databases.

      Running command on sql is fine and giving expected value. running this command with code (given in my post) is not giving expected result. it is giving "11/09/12" instead of "11/9/2012 3:19:18.000 PM" I am using DBI.

        Your code posted does not use DBI. You've ignored the advice I gave. Given your clamed CV/resume on your homenode basic debugging should be a very famliar concept to you. Simply restating the output you get without doing any additional work, or offering any more insight into your problem isn't going to help you. See How do I post a question effectively?.

Re: Not able to find dateTime value using sql
by Anonymous Monk on Nov 14, 2012 at 14:15 UTC
    Add
    print STDERR "$origina_tradeTime\n";
    to your program to see what the actual character-string is ... then, actually rewrite your code to use DBI!
Re: Not able to find dateTime value using sql
by NetWallah (Abbot) on Nov 14, 2012 at 14:47 UTC
    You should probably use the 'DATEFORMAT' function instead of 'convert'.

                 "By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."           -Confucius

Re: Not able to find dateTime value using sql
by karlgoethebier (Curate) on Nov 14, 2012 at 18:17 UTC

    A little another hint: I think that it's not a bad idea to put your SQL queries into stored procedures wherever this is possible.

    Then you can call the stored procedures via DBI and you don't need to worry anymore about formatting your SQL statements in perl - what often is an annoying task.

    This is convenient and the performance will also be better.

    Regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

Re: Not able to find dateTime value using sql
by sundialsvc4 (Monsignor) on Nov 14, 2012 at 22:27 UTC

    The entire problem here (seems to me at first glance to be) a matter of, first, getting the executed command to generate the full datetime value as a string; and, second, interpreting that string.   All of which ceases to be a problem when you approach the task using DBI.   Now, you can obtain the value directly, i.e. in binary form.   And you’re not executing any shell-command to do it, which can very quickly become prohibitive, say in a loop.   So, to me, this logic as-conceived definitely should be abandoned, not patched.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1003790]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (19)
As of 2014-08-01 14:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (25 votes), past polls