Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to pass date to SQL server SP?
Top Forums Shell Programming and Scripting How to pass date to SQL server SP? Post 302924459 by Rahul Raj on Sunday 9th of November 2014 11:33:22 PM
Old 11-10-2014
Quote:
Originally Posted by blackrageous
I gather you are using the sqlcmd client on the UNIX side. The sqlcmd -v switch (at least on the dos version is for the purpose of passing variables into the embeddeded SQL input file.

Please confirm that you are running sqlcmd on unix. If so what operating system / version of Unix?
HI Blackrageous,

Yes, I am passing the date variable through unix. We are using Linux version.

I am able to pass the variable but still will need suggestions in case of improvements.

- passed the date while executing the shell as a argument.
- Checked whether the date is passed or else moved the environment variable which contains a date in format yyyy-mm-dd. THis will go into variable_passed_date1 local variable
- used the export variable command for variable passed_date1.
Now, I am able to use varaiable_passed_date1 in the input SQLCMD sql file.
- Also, in the SQL server SP, I have defined the argument as datetime and using the CONVERT function to convert it into YYYY-mm-dd HH:MM:SSS (something of this format).

This is working, but the HH:MM:SS.SSS is 0 and I want it to be set as a high value so that all records of the particular date are captured.

---------- Post updated at 11:33 PM ---------- Previous update was at 11:31 PM ----------

Quote:
Originally Posted by DGPickett
Usually, you choose a string format that is compatible, as passing binary date-time is a compiled code deal, not scripting. I like "YYYY-mm-dd HH:MM:SS.ssssss" but your SQL engine may vary. Sometimes you need a conversion tool like a CONVERT() function.
Thanks for the reply!

I have declared the date variable as date and later converting it into datetime using CONVERT. This is working fine now.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pass multiple variables to SQL script

I am trying to close of multiple users in an Oracle database. Each users has records in multiple tables what I need to do is use a script that call each SQL seperately passing either CLI arguments or gathered arguments from the users during run time. ## Accept variable(s) from the command line... (1 Reply)
Discussion started by: jagannatha
1 Replies

2. Shell Programming and Scripting

Pass variable to sql

Please help. I got these error. I'm try to pass variable extract from data-file.txt to sql file(select.sql). cat: cannot open select cat: cannot open * cat: cannot open from cat: cannot open user cat: cannot open where cat: cannot open name=$list; #!/bin/bash list=`sed q... (3 Replies)
Discussion started by: killboy
3 Replies

3. Shell Programming and Scripting

Pass a variable to SQL script

Hi Guys, I like to pass a variable to a sql file in a unix script.. I tried a below code.. var=200903 db2 -vf test.sql 200903 test.sql is as below. select * from db2.users where quarter = $1; Please tell me where i go wrong.. Thanks in advance, Magesh (2 Replies)
Discussion started by: mac4rfree
2 Replies

4. Shell Programming and Scripting

Pass the first date and last date of previous month

Hi All, I need to run a job every month at the beginning of the month which is scheduled through autosys, lets say on 03/01/2010. I need to pass the last month's i.e February's first_date = 02/01/2010 and last_date = 02/28/2010 as variables to a stored procedure. Can somebody please pass... (2 Replies)
Discussion started by: vigdmab
2 Replies

5. Shell Programming and Scripting

How to pass value from unix shell to sql session?

Here is the situation, I have file from which I am reading records line by line. Based upon some condition I need to pass a field from a sql session to get some data from a table. I don't have any idea how to do it.... (1 Reply)
Discussion started by: mady135
1 Replies

6. Shell Programming and Scripting

Pass perl array to SQL oracle

Hello, Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg : #!/usr/bin/perl @v_array = (1,2,4,5,6,8); $db_userid = 'ni71/ni711'; $bufTPO = qx{ sqlplus -s << EOF $db_userid set verify off set feedback off set... (1 Reply)
Discussion started by: arunshankar.c
1 Replies

7. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

8. Shell Programming and Scripting

How to pass string into sql query?

Hi Gurus, I have a request which needs to pass string into sql. dummy code as below: sqlplus -s user/password@instance << EOF >>output.txt set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep , select emp_no, emp_name from emp where emp_no in ('a', 'b', 'c'); exit;... (4 Replies)
Discussion started by: ken6503
4 Replies

9. Shell Programming and Scripting

How to pass a shellscript variable to a sql file?

Hi, i wan't to pass a shellscript variable to a sql file. a.sql select $field from dual; the way i am calling this is through sqlplus field_name="sysdate" sqlplus -s username/password@hostname:port/servicename <<EOF @a.sql $field_name EOF (4 Replies)
Discussion started by: reignangel2003
4 Replies

10. UNIX for Advanced & Expert Users

To pass one month range in sql script

Hi Guys, i am having .sql script which inserts data from one table to another table based on date condition, i need to pass range on based on how many number of months, for e.g set timing on; whenever sqlerror exit failure; spool myscript.log append accept start_date... (7 Replies)
Discussion started by: rohit_shinez
7 Replies

LEARN ABOUT PHP

date_parse_from_format

DATE_PARSE_FROM_FORMAT(3)						 1						 DATE_PARSE_FROM_FORMAT(3)

date_parse_from_format - Get info about given date formatted according to the specified format

SYNOPSIS

       array date_parse_from_format (string  $format, string  $date)

DESCRIPTION

	Returns associative array with detailed info about given date.

PARAMETERS

	      o $format
		- Format accepted by DateTime.createFromFormat(3).

	      o $date
		- String representing the date.

RETURN VALUES

	Returns associative array with detailed info about given date.

EXAMPLES

       Example #1

	      date_parse_from_format(3) example

	      <?php
	      $date = "6.1.2009 13:00+01:00";
	      print_r(date_parse_from_format("j.n.Y H:iP", $date));
	      ?>

	      The above example will output:

	      Array
	      (
		  [year] => 2009
		  [month] => 1
		  [day] => 6
		  [hour] => 13
		  [minute] => 0
		  [second] => 0
		  [fraction] =>
		  [warning_count] => 0
		  [warnings] => Array
		      (
		      )

		  [error_count] => 0
		  [errors] => Array
		      (
		      )

		  [is_localtime] => 1
		  [zone_type] => 1
		  [zone] => -60
		  [is_dst] =>
	      )

SEE ALSO

       DateTime.createFromFormat(3), checkdate(3).

PHP Documentation Group 												 DATE_PARSE_FROM_FORMAT(3)
All times are GMT -4. The time now is 04:58 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy