SQLserver dateconversion english/dutch

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

insomniux - 18 Mar 2008 19:17 GMT

Hi,
I need to compare two varchar columns containing date values. One
column contains the 'dd-mmm-yyyy' format in english, the other in
dutch. Problem is that in march,may and october the 'mmm' format is
different. So essentially, the question is: how can I compare '10-
mar-2008' with '25-mrt-2007' (should yield about 350 days difference.
The convert() function I'm using chokes on the dutch format.
Thanks,
Mike

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 18 Mar 2008 20:12 GMT

> I need to compare two varchar columns containing date values. One
> column contains the 'dd-mmm-yyyy' format in english, the other in
> dutch.

If it is a datetime column, then that is not what the column "contains" --
it is only what your client tool is showing you.

> The convert() function I'm using chokes on the dutch format.

Can you show what you tried, and explain what "chokes" means? Why don't you
try using an unambiguous date format for literals, like YYYYMMDD?

A

Reply to this Message

insomniux - 18 Mar 2008 22:03 GMT

On Mar 18, 8:12 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:

> > I need to compare two varchar columns containing date values. One
> > column contains the 'dd-mmm-yyyy' format in english, the other in
[quoted text clipped - 9 lines]
>
> A

Aaron,
You're absolutely right. However I have no control over this database,
I just need to do some analyzing. I'm faced with the problem that I
need to compare apples and pears, but still I need to do that....
The database is accessed by different frontends on differend language
platforms (web/msAccess/Dutch/English, all mixed). To avoid the
confusion between dates like 3-5-2008 and 3/5/2008, the date is stored
with 'mmm' format.
I use the convert(datetime,DateString,105) function. That function
does not like the 'mrt' part of the month (says something like string
cannot be converted, can't remember exactly)
I was wondering if there is an easy trick to (e.g. a function) to
change the locale of the date value, or that tells convert that it
should use another locale.
Mike

Reply to this Message

Tom Cooper - 18 Mar 2008 20:37 GMT

As Aaron says, it is best to store datetimes in a datetime column. And if a
character literal is to contain a date, use an unambigous format like
yyyymmdd.

If you are getting these inputs from some other system via an ETL process,
first load the data into a staging table and then convert the values to a
datetime column, checking for errors as needed. Then load the verified and
converted data into your real tables. Then you can do operations on this
converted data.

However, if you decide you want to compare the data as is, you could do
something like the following:

Create Function dbo.EnglishAndDutchToDateTime(@Input char(11))
Returns datetime As
Begin
Return Convert(datetime,
Left(@Input, 2) + ' ' +
Case Substring(@Input, 4, 3)
When 'mrt' Then 'mar'
When 'mei' Then 'may'
When 'okt' Then 'oct'
Else Substring(@Input, 4, 3) End
+ ' ' +
Right(@Input, 4),
106)
End
go
Select DateDiff(dd,
dbo.EnglishAndDutchToDateTime('25-mrt-2007'),
dbo.EnglishAndDutchToDateTime('10-mar-2008'))
go
Drop Function dbo.EnglishAndDutchToDateTime

Of course, if your server's language is Dutch, do the month name conversions
the other way. And this method will give an error if an invalid date is
passed. You would have to add additional code to make it bullet proof
against invalid dates. And, you may have performance problems.

Tom

> Hi,
> I need to compare two varchar columns containing date values. One
[quoted text clipped - 5 lines]
> Thanks,
> Mike

Reply to this Message

insomniux - 18 Mar 2008 22:14 GMT

On Mar 18, 8:37 pm, "Tom Cooper"
<tomcoo...@comcast.no.spam.please.net> wrote:

> As Aaron says, it is best to store datetimes in a datetime column. And if a
> character literal is to contain a date, use an unambigous format like
> yyyymmdd.

Completely agree. Problem is that it also should be 'human readable'
Although, of course the yyymmdd format would be the most natural,
there are few people who would immediately understand it.

> If you are getting these inputs from some other system via an ETL process,
> first load the data into a staging table and then convert the values to a
> datetime column, checking for errors as needed. Then load the verified and
> converted data into your real tables. Then you can do operations on this
> converted data.

Unfortunately I have no authority to create/fill temporary tables.
Only have access to the raw data. Also I prefer to do the analysis
within 1 single step.

> However, if you decide you want to compare the data as is, you could do
> something like the following:
[quoted text clipped - 19 lines]
> go
> Drop Function dbo.EnglishAndDutchToDateTime

This is great. And simple (of course needs some checking on basic
validity of the date value)! Wonder why I have not thought if this
approach. Must have something to do with intelligence.....
Thanks Tom!

> > Hi,
> > I need to compare two varchar columns containing date values. One
[quoted text clipped - 7 lines]
>
> - Show quoted text -

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 19 Mar 2008 00:18 GMT

> Completely agree. Problem is that it also should be 'human readable'

That is ABSOLUTELY no excuse for STORING a date/time as a character string.
If you want it human readable, then you can convert it to any of about a
hundred character string formats when you *SELECT* the data. You can even
make it easy by putting the calculation in a view or UDF. Or you can format
the date on the client. The storage doesn't have to be human readable. Did
you know that a datetime value is actually stored as two integers? Even
Management Studio does this conversion to human readable format for you, and
doesn't store it in an inefficient, unintuitive, ambiguous or problematic
way.

A

Reply to this Message

insomniux - 19 Mar 2008 14:14 GMT

Again, completely agree.
But fact is that the data IS stored (I have no choice) as a string by
another application over which I have nothing to decide. It is a
string because the same column is also used for other data types
(text, numbers,etc) within other contexts.

Meanwhile I succeeded to prepare the SQL statement to do the desired
calculation based on the suggestions in this thread. Problem is
solved.
All thanks for the suggestions
Mike

Aaron Bertrand [SQL Server MVP] schreef:

> > Completely agree. Problem is that it also should be 'human readable'
>
[quoted text clipped - 9 lines]
>
> A

Reply to this Message

Russell Fields - 18 Mar 2008 22:13 GMT

Mike,

I believe that you can change your language in the code to work through
this. Look up SET LANGUAGE in the Books Online.

Some thing like the untested code below:

DECLARE @MyDutchDate DATETIMEDECLARE @MyEnglishDate DATETIMESET LANGUAGE
DutchSELECT @MyDutchDate = DutchDateString FROM MyTableSET LANGUAGE
us_englishSELECT @MyEnglishDate = EnglishDateString FROM MyTableSELECT
DATEDIFF (Day, @MyDutchDate, @MyEnglishDate) AS

> Hi,
> I need to compare two varchar columns containing date values. One
[quoted text clipped - 5 lines]
> Thanks,
> Mike

Reply to this Message

Russell Fields - 19 Mar 2008 14:29 GMT

Just to clean up the formatting nightmare of my previous post. (It didn't
look like that when I was looking at it.)

DECLARE @MyDutchDate DATETIME
DECLARE @MyEnglishDate DATETIME

SET LANGUAGE Dutch
SELECT @MyDutchDate = DutchDateString FROM MyTable
SET LANGUAGE us_english
SELECT @MyEnglishDate = EnglishDateString FROM MyTable

SELECT DATEDIFF (Day, @MyDutchDate, @MyEnglishDate) AS DifferenceInDate

RLF

> Mike,
>
[quoted text clipped - 16 lines]
>> Thanks,
>> Mike

Reply to this Message