August 2, 2010 at 5:50 am
I have a table in SQL Server 2005 SP2 with the following date columns.
- Created
- Updated
- NextRun
- LastRun
All columns have datetime as data type.
When I write values into the table through an external application, columns Created and Updated have date syntax YYYY-MM-DD, while columns NextRun and LastRun have syntax YYYY-DD-MM.
As long as day and month are not bigger than 12, everything runs smooth, but when the day is 13 or bigger, the application generates an out-of-range error on the date format.
Why are the date values different?? How to resolve this annoying error?
August 2, 2010 at 6:30 am
Hi michielbijnen,
This is because the sql server datetime has a format 2010-08-02 17:27:54.590 ie YY-MM-DD, if u insert 13 on the month's space it will throw error.Change ur date format to YY-MM-DD from the application side.This will solve the issue.If u want to do it in SQL SERVER SIDE you can use substring and stuff function to relplace the day to month and vice-versa...
Hope u got the IDEA
Regrads
Varun R
August 2, 2010 at 6:42 am
Thanks Varun.
Problem is that the application inserts dates with YYYY-DD-MM, but somehow in SQL Server it is converted to YYYY-MM-DD.
Hoep you have another clue.
reg,
Michiel
August 2, 2010 at 1:00 pm
michielbijnen (8/2/2010)
Thanks Varun.Problem is that the application inserts dates with YYYY-DD-MM, but somehow in SQL Server it is converted to YYYY-MM-DD.
Hoep you have another clue.
reg,
Michiel
I'm curious what the application is. That's not a valid date format that I know of. IMO, the best fix is to get whoever wrote the app to use a valid date format.
August 2, 2010 at 3:32 pm
Here is a step by step conversion of the application format to SQL format
** Note this assumes that the application is passing the date in char format not some other. ** <<added per bteraberry comment @ 5:40 PM>>
DECLARE @Din VARCHAR(10) --Application format YYYY-DD-MM
DECLARE @Dout VARCHAR(10) --SQL Format YYYY-MM-DD.
DECLARE @Yr VARCHAR(4)
DECLARE @Day VARCHAR(2)
DECLARE @Mon VARCHAR(2)
SET @Din = '2010-13-07'
--SET @Din = '2010-13-7' -- Slightly different format
SET @Yr = SUBSTRING(@Din,1,CHARINDEX('-',@Din)-1)
SET @Day = SUBSTRING(@Din,CHARINDEX('-',@Din)+1,2)
SET @Mon = SUBSTRING(@Din,CHARINDEX('-',@Din)+4,2)
SET @Dout = @yr +'-'+@Mon+'-'+@Day
SELECT @Din AS 'Date input',@Dout AS 'SQL Format'
Result:
Date input SQL Format
2010-13-07 2010-07-13
Now you can simplify the code by combining the multiple SET statements into one. Posted this to show in detail what was being done in an easy to read format for example:
SET @Dout =SUBSTRING(@Din,1,CHARINDEX('-',@Din)-1)+
'-'+SUBSTRING(@Din,CHARINDEX('-',@Din)+4,2)
+'-'+SUBSTRING(@Din,CHARINDEX('-',@Din)+1,2)
August 2, 2010 at 3:40 pm
Ron,
I was thinking about this method too, but if the issue is on the app side it's quite possible that either the parameters' datatypes cannot be changed (because they're set as datetime on the app side) or the app is using a string to execute in lieu of a parameterized proc. I'm still curious to know more about the app.
August 2, 2010 at 3:53 pm
August 2, 2010 at 4:46 pm
I think some knowledge on the app and the desk top setup is needed. The last time I had this sort of an issue it turned out there was a variety of language support at the desk top level, some users using English(US) and others english(UK). Key difference (apart from some spelling) is date formats, US is MM-DD-YYYY and UK is DD-MM-YYYY.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply