Solved

Stored Procedure Date Problem

Posted on 2000-04-13
16
754 Views
Last Modified: 2012-06-21
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
Comment
Question by:Bundles031799
  • 5
  • 5
  • 4
  • +2
16 Comments
 
LVL 7

Expert Comment

by:simonsabin
ID: 2711500
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2711509
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711565
I always pass dates in the format
YYYY/MM/DD and never have problems!!!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Expert Comment

by:simonsabin
ID: 2711579
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711598
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2711615
You can sepcify what you want, you can even swap your months around to really annoy people
0
 

Author Comment

by:Bundles031799
ID: 2711640
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 2711674
usp_MySP '1 Feb 2000'

What do you have at the moment?
0
 

Author Comment

by:Bundles031799
ID: 2711705
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711735
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
 

Author Comment

by:Bundles031799
ID: 2711860
Adjusted points from 40 to 60
0
 

Author Comment

by:Bundles031799
ID: 2711861
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2711876
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
 
LVL 1

Accepted Solution

by:
ATM earned 60 total points
ID: 2711907
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
 
LVL 5

Expert Comment

by:dtomyn
ID: 2711914
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
 

Author Comment

by:Bundles031799
ID: 2712315
I'm accepting ATM as i used that answer and it has solved my problems completely! Cheers for the help all!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL with ODBC 5 34
MS SQL Merging data from table into another table 1 31
SQL Server 2012 r2 - Sum totals 2 21
Help in Bulk Insert 9 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now