• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

Stored Procedure Date Problem

Most of my problems with queries and code revolve around date issues :-)

I am running a stored procedure in SQL 6.5 from an Access 97 Pass Thru Query. I want to pass 2 dates to the SP in dd/mm/yyyy format (StartDate and EndDate). I have no problems passing normal data thru to an SP. I only have problems passing dates thru to an SP. Basically I get errors regarding either formatting issues or datatype conversion issues.

How do I do this correctly?
0
Bundles031799
Asked:
Bundles031799
  • 5
  • 5
  • 4
  • +2
1 Solution
 
simonsabinCommented:
What is the default date format of your machine?
What is the language of the server?
run sp_helplanguage

if you only have us_english the dates need to be in mm/dd/yyyy
0
 
simonsabinCommented:
Or you can add another language with

sp_addlanguage       'British','British',
            'January,February,March,April,May,June,July,August,September,October,November,December',
            'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec',
            'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday',
            'dmy',
            1
go
sp_configure 'default language',1
go
RECONFIGURE WITH OVERRIDE
go

sp_helplanguage
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I always pass dates in the format
YYYY/MM/DD and never have problems!!!
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
simonsabinCommented:
The safest is to use include the month in short format i.e Jan , Feb as this is not dependant on the dmy mdy format specified.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simonsabin:
i'm sorry to again correct you, but if you install your SQL Server on German (or other language) NT Server, this will not work for all months
DEC vs DEZ
0
 
simonsabinCommented:
You can sepcify what you want, you can even swap your months around to really annoy people
0
 
Bundles031799Author Commented:
Can i have the exact Terminology you would use in both the Pass-Thru query and in the SP itself to correctly pass the dates please?
0
 
simonsabinCommented:
usp_MySP '1 Feb 2000'

What do you have at the moment?
0
 
Bundles031799Author Commented:
Exec sp_webtrials2 "01/01/2000", "31/01/2000"

The values are being posted into @StartDate datetime,
@EndDate datetime


The above is completely incorrect syntax hence i need the help but my date/time syntax has always caused me grief!!! ;)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Exec sp_webtrials2 '2000/01/01', '2000/01/31'

BTW
1) I think you have to watch your query condition, using datetimes, maybe you post your WHERE clause, i check it
2) if you want to have the query to get dates only for one month, then i may suggest another method.
0
 
Bundles031799Author Commented:
Adjusted points from 40 to 60
0
 
Bundles031799Author Commented:
What other method do you suggest for just one month then? date part and then just pass a the month as a number? Let me know if this is it and if so best method to do so.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Absolutely:
Where DatePart('mm',DateCol) = @month
AND DatePart ( 'yyyy',DateCol) = @year

BTW, i prefer using DatePart and DateDiff functions over simple <=> operations (by experience)
0
 
ATMCommented:
construct date string in format "DD/MM/YYYY", pass this string as varchar(10), in stored procedure use
SELECT @mydate=CONVERT(datetime,@mystrdate,103)
103 is a code for this type date.
Refer to CONVERT definitions in T-SQL help for other date codes.
0
 
dtomynCommented:
Bundles,
I have hit the same sort of issues here in Canada.  Now, I always send SQL Server a date string (of ex. Feb 2, 2000) then at the beginning of the stored procedure I do something like this:
@RunTimeString as varchar(25)
AS
set dateformat ymd
DECLARE @RunTime as datetime
SET @RunTime = CAST(@RunTimeString as datetime)

I am not saying the above is perfect, but it has completely solved any date issues for me.
0
 
Bundles031799Author Commented:
I'm accepting ATM as i used that answer and it has solved my problems completely! Cheers for the help all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now