date format problems with xp_cmdshell
-
Hi - this IS about SQL but I'm not using 2005, just 2003 (version 8.00.2039). I couldn't see anywhere else to ask this question.
I have SQL statements saved in a stored procedure. When I run them in Query Analyzer everything is fine.
But, I want to click a button on a Web page to kick off that SQL and take the results and copy them onto the network (this is to run a report; the SQL output is run through a text formatter-distributer and e-mailed back to the person who clicked on the ASP checkbox).
So, I'm using a stored procedure which reads: xp_cmdshell osql sp_reportSP blah blah. When my statements run like this, implicit conversion is assuming US date formats. So, char(10) format 103 "13/12/2006" is read as mm/dd/yyyy and the statement errors. (For reasons a bit long to go into here, I must use character dates because date-time values won't work with my db.)
Since implicit conversion in QA itself assumes dd/mm/yyyy dates, where must I look to find the default that is read whenever osql (or, for that matter, isql) is invoked in xp_cmdshell?
Thanks, Sharon.
-
... So, I'm using a stored procedure which reads: xp_cmdshell
osql sp_reportSP blah blah ...within your sp you can use convert(char(10),yourdate,101) to provide a datestring for your cmdshell command.
mind me asking why you use xpcmdshell to conect to the local instance and execute a sp ? You can do the same (and even more secure) using linked servers or oledb calls if you need to go remote, without opening cmdshell.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi - I must admit that this solution is not mine - my IT department came up with it. The need, as in my original post, is that we have a page on our Intranet which lists the reports one can run, and a checkbox for each that can be ticked to select. The "report" is two pieces, the SQL statements (saved in a stored procedure) the results of which are output as a text file which is picked up by software which follows rules to format ASCII text files and distribute the final Word document (in this case, via e-mail to the requestor).
My IT team came up with an ASP page so that if the checkbox object is checked status, it runs a SQL command which is a stored procedure composed of xp_cmdshell osql and the underlying stored procedure which contains the SQL statements to select the data. I'm out of my depth with ASP and so am unable to do more than supply you with sample code (below; this feeds ).
The problem really does appear to be that char date fields are being read as mm/dd instead of dd/mm which seems to indicate that this process is doing implicit conversion of character-to-date which is quite different from the implicit conversion done when the underlying SP is run in QA. Where do I look for that U.S. date format default for xp_cmdshell?
create procedure hk_spDBCN as
exec master..xp_cmdshell
'osql -E dbname -w 500 -Q "exec dbname..hk_spCredNotRegDaily" -o OutputDestination'
If chkDBCN.Checked then
cmdDBCN = New SqlCommand("hk_spDBCN", objConn)
cmdDBCN.CommandType = CommandType.StoredProcedure
objConn.Open()
cmdDBCN.ExecuteReader()
objConn.Close()
Thanks, Sharon.
-
as you can see, dateformat is a client-problem. What I mean is that the format is set using the client-settings for the logged in user !
That is why dateformatting is a front-end problem, not a database problem. One should handle dateformat and date-handling front-end-side ! Easiest is to have your programmers use parameters to handle this. This way, when they put a datetime-variable in a datetime-type-parameter, the backend application knows how to handle it properly.
From what I can see of your code, there is no inputparameter provided to the sqlcommand ! ??
Are you storing dates in character columns in stead of (small)datetime columns ?
if yes, that's where it went wrong. Use the correct datatype for your columns.
How to solve it for your current data ? You could perform an update query to move the dateparts to their correct position, if you know wich dates are wrong. (The obvious ones being those who fail on an isdate function).
Your programmers should correct their code so this problem is handled client-side and correct data is inserted in your db.
If you can have the column altered using a datetime-type datatype !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi - let me start again, this might be easier:
1) Need today's date so use function GETDATE().
2) SQL Server only has datetime fields (no date-only fields) so that is returned as 2006-12-20 10:49:01.783
3) The software which I am using (commercial application; cannot modify anything about the db) writes all dates as date + midnight i.e. 2006-12-20 00:00:00.000
4) If the GETDATE() result is used as-is in a WHERE clause as the Start Date, the system doesn't pick up any records dated 20 Dec.
That is because they are actually "dated" before the start date/time.
5) I can convert GETDATE() to char(10) format 103 to get 20/12/2006 and I can use that character value as my start date in SQL.
SQL's implicit conversion recognises that as a date and will re-convert it to 2006-12-20 00:00:00.000 in processing the statement in Query Analyzer.
6) HOWEVER: SQL's implicit conversion, when the statement is run as part of xp_cmdshell (either as isql or osql), is reading the characters 20/12/2006 as mm/dd/yyyy and (in this particular example) returning an error.
So, QA implicitly converts char-to-date as dd/mm/yyyy. But xp_cmdshell thinks it is mm/dd/yyyy. So, it seems like there must be a setting someplace which says US Date Format as the default. This setting is obviously not seen by QA but it is controlling xp_cmdshell.
Any idea where that setting is? Thanks, Sharon.
-
Much simpler than you think :
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
--'2006-12-20 00:00:00.000'
-
Hi Ninja - wow, is that ever a good workaround! I've been focusing so much on the "woods" that I missed the "trees". Yes, of course by converting GETDATE() to a date with midnight time, I get the results I want.
Now, of course, though, that pesky US date format default somewhere in SQL Server is still bugging me! I know that my particular need has now an answer, but won't that default still cause me grief in other ways? It would be easiest (I think) to change that default so that everything is d/m/y format.
Or is that just not possible? Thanks, Sharon.
-
I haven't reread the whole thread but here's what my guts is telling me :
You don't need to get out of the server to execute something in the same server.... maybe dynamic sql may be required if this needs to be run on different databases but that's the worst that could happen. I'm sure you can figure out a way to simply call the sp from the application and get the same results.
PS. A SP can raise an alert, which alert can fire a job which can do, well anything you need it to do on the server.
-
fwiw
login using your sqlserver service account and alter its local user settings. and hope nothing else breaks due to this.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply