How to pass date to SQL server SP?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pass date to SQL server SP?
# 1  
Old 11-05-2014
How to pass date to SQL server SP?

HI,

I am calling a stored procedure to archieve records from the DB based on a date condition. The syntax for the same is as below:
shell script - xyz.sh
Code:
 sqlcmd -s<servername> -U <username> -p<password> -I <embedded SQL input file>

The contents of the Embedded SQL INput file are as below
Code:
exec <SP Name> '$date_var'

Query 1: Can someone please advice how can I pass the date through the shell script so that it can be passed to the Embedded SQL input file's Date_var variable during the runtime.

Query 2: Also, I want to check if the user has passed the date paramaeter or else will use the system date to delete the records.

I am thinking of the below code for the same

Code:
 if [ $# -eq 0 ]; then
    echo "No arguments provided"
      set date_var='$(date)'
     else 
      set date_var=$1
fi

Is the above sysntax correct. I want to pass the date in yyyy-mm-dd format.
Would appreciate if a prompt response is provided

Thanks in advance!
# 2  
Old 11-05-2014
Is there a -v switch for sqlcmd, see man pages. What is the operating system and sql version.
# 3  
Old 11-05-2014
Hi Blackrageous,

Thanks for the reply!

it's SQL SERVER 2008 R2 version.

I will check on the -v switch and let you know. But my doubt is how to pass the date from the unix script to the input file containing the sqlcmd command.

Code:
 
 ./XYZ.sh '2014-11-06'

XYZ.sh

Code:
 
 sqlcmd -s<servername> -U <username> -p<password> -I <embedded SQL input file>

Input File
Code:
 
 exec <SP Name> '$date_var'

I want to pass the date '2014-11-06' to date_var
# 4  
Old 11-07-2014
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?
This User Gave Thanks to blackrageous For This Post:
# 5  
Old 11-07-2014
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.
This User Gave Thanks to DGPickett For This Post:
# 6  
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.
# 7  
Old 11-12-2014
Yes, date-time and the BETWEEN clause do not get along. The cleanest thing is to say "DTColName >= YourDate AND DTColName < YourDateNext" so trailing nanoseconds do not fall into a hole. Ditto for "find ... -newer X ! -newer Y" and file dates! You do not want the overhead and index-disabling "CONVERT(DTColName,Date) = YourDate" (although some RDBMS allow you to index function expressions!)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
Login or Register to Ask a Question

Featured Tech Videos