The contents of the Embedded SQL INput file are as below
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
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
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.
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?
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.
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 ----------
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.
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!)
I am currently using the date criteria as yyyy-mm-dd hh:mi:ss.mmm in the between condition for the current date and add_date(mm,-3) 3 months before date. Can you please suggest if with this condition also the records will be missed between the 2 dates.
Even if you know the granularity of the local datetime, in an optimistic world you would love it to be portable. In place of worrying about the precision, which might be a very odd fraction not easily accessible by string number conversions, kiss "BETWEEN" goodbye and use "ColDate >= Date and ColDate < (Date+1day)". It has no hole. The SQL compile will just take your BETWEEN and turn it into a similar pair of predicates. There is no run time advantage. If indexed, it will search for the low value and read to the high value in the index, a range scan.
The SQL gods should have made a way to say if BETWEEN end points were included or not, like those open/dark end line point dots in math class. Many languages have these misconceived pitfalls to avoid, except when taking mindless proficiency tests to get hired.