Not able to find dateTime value using sql

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
[download]

but output coming only:

11/9/2012
[download]

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";
[download]

Comment on Not able to find dateTime value using sql Select or Download Code

Replies are listed 'Best First'.
Re: Not able to find dateTime value using sql by marto (Cardinal) 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.	[reply]
Re^2: Not able to find dateTime value using sql by bimleshsharma (Beadle) on Nov 14, 2012 at 13:37 UTC
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.	[reply]
Re^3: Not able to find dateTime value using sql by marto (Cardinal) on Nov 14, 2012 at 13:42 UTC
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?.	[reply]
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.	[reply]
Re^2: Not able to find dateTime value using sql by bimleshsharma (Beadle) on Nov 14, 2012 at 13:08 UTC
Yes, SQL output is not correct if running with code. While same sql query is returning fine at sql command prompt.	[reply]
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.	[reply]
Re: Not able to find dateTime value using sql by NetWallah (Canon) 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	[reply]
Re: Not able to find dateTime value using sql by karlgoethebier (Abbot) 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ť	[reply]
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!	[reply]
Re: Not able to find dateTime value using sql by sundialsvc4 (Abbot) 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.


Keep It Simple, Stupid
	PerlMonks