October 8, 2007 at 10:08 am
Hi,
I'm running SQL Server 2005. The installation is set to use the dd/mm/yyyy (British date format). I have a database that appears to be running under the same format. I've checked the data in the tables and all appears to be the same. However, any stored procedures I write for this database, the date format of any input parameters needs to be in mm/dd/yyyy (US date format)
Does anyone know how I can fix this issue? I've tried adding SET DATE FORMAT dmy in the stored procedure, but this doesn't work.
October 8, 2007 at 10:34 am
you can change your us date to brit format by
convert(char(10), , 103)
or convert to us date using
convert(char(10), , 101)
October 8, 2007 at 12:40 pm
Thanks for your response. However, should I really be having to convert a date back to the format I want. I just can't understand why this database is running like this. Very confusing. All other databases on this instance work fine apart from this one
October 8, 2007 at 1:51 pm
The server AND each database have their own collation settings. Could it be that that DB is set incorrectly?
Take a look at this over thread: http://www.sqlservercentral.com/Forums/Topic305998-266-1.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 8, 2007 at 2:01 pm
How do you know the stored procedures are expecting US Format? What happens if you run a query against the table using a date field in where clause? How are you creating the SP's (Query Analyzer in SSMS, Visual Studio, Some other tool)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2007 at 2:28 pm
I was doing this for example
DATEADD(m,5,@pdteStartDate)
and 5 months was being added to the day component rather than the month component of @pdteStartDate, however when I passed in the same parameter but in US format it worked fine.
The database wasn't created by me but is the backend of an off-the-shelf helpdesk call management system. The collation is set to SQL_Latin1_General_CP1_CI_AS wheras the server default is Latin1_General_CI_AS. Would this make a difference?
October 8, 2007 at 2:50 pm
Got it to work doing this as recommended by Joel
SET @pdteStartDate = CONVERT(char(10), @pdteStartDate, 103)
October 8, 2007 at 3:13 pm
Is the parameter/variable defined as datetime or smalldatetime?
AS far as the collations they are the same. Both are US English Case Insensitive and Accent Insensitive and I do not believe they affect dates.
The date comes from the language setting. You can issue the Set Language to change the language for a session.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2007 at 3:16 pm
I've tried both datetime and smalldatetime and have the same issue with both
October 9, 2007 at 3:19 am
Seems like the confusion is about how to enter the date into the variable?
And also maybe combined with confusion on how a datetime is presented when retrieved from a variable or a column.
The important part is to ensure that a date is parsed and written to the db as intended.
When we use for example '/', this becomes very risky business.
My recommended format is yyyymmdd (with no delimiters)
It's always safe.
Here's a good article on the subject.
/Kenneth
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply