Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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