Different date formats in single table

Question

Different date formats in single table

michielbijnen

Ten Centuries

Points: 1308
More actions
August 2, 2010 at 5:50 am

#225635

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

varunfilim Mr or Mrs. 500 Points: 594 More actions · Answer 1

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

michielbijnen Ten Centuries Points: 1308 More actions · Answer 2

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

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 inputSQL Format

2010-13-072010-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)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 5

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

bteraberry

Edited my post to note my assumptions - per your comments at 5:40 PM

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

dogramone Hall of Fame Points: 3216 More actions · Answer 7

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.