Non-US date formats and SQL 
Author Message
 Non-US date formats and SQL



Quote:
> I'm writing an app where I need to select a group of records from a
> recordset that fall between two dates.  The user enters the dates into text
> boxes on a form, and I build a SQL select statement including the dates.
> Here is the code:

> selstr = "SELECT * FROM Anal_hist WHERE equip_id = " &
> dataEquipment.Recordset!equip_id
> selstr = selstr & " AND dataAnalise > #" & DateValue(boxNumdays.Text) & "#"
> selstr = selstr & " AND dataAnalise <= #" & DateValue(boxEndDate.Text) &
> "#;"
> dataAnalhist.RecordSource = selstr
> dataAnalhist.Refresh

For some strange reason, maybe something to do with the American
attitude to things not American :) (not really knocking the US,
good ole Microsoft), dates should be mm/dd/yy to suit Access/SQL
Server.

I for one dislike this format, and it's ambiguity, so I always use
a non-ambiguous format, so

SQL = SQL & " AND DateField > #" & Format(txtDate.Text, "dd mmm yyyy") & "#"
which produces, for example
            " AND DateField > #21 May 1997#"

and dispense with the TOTALLY redundant DateValue.
This apparently works for me across Europe with its variety of
date formats.

Also, you could use the 'BETWEEN xxxxx AND yyyyy' sql structure,
but note that its inclusive.

HTH
--
ernest     at Noel Systems, near Kingston-upon-Thames (UK)
           (if replying by email, remove the * from address)



Sun, 07 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL

Hi everyone,

I'm writing an app where I need to select a group of records from a
recordset that fall between two dates.  The user enters the dates into text
boxes on a form, and I build a SQL select statement including the dates.
Here is the code:

selstr = "SELECT * FROM Anal_hist WHERE equip_id = " &
dataEquipment.Recordset!equip_id
selstr = selstr & " AND dataAnalise > #" & DateValue(boxNumdays.Text) & "#"
selstr = selstr & " AND dataAnalise <= #" & DateValue(boxEndDate.Text) &
"#;"
dataAnalhist.RecordSource = selstr
dataAnalhist.Refresh

I am using VB 4.0 32 bit under NT 4.0, US International keyboard layout,
and short date format "dd/mm/yy" .  Here is the problem:  when the dates
are "ambiguous", the SELECT returns no records.  For example:
if boxNumdays.Text = "12/05/97" and boxEndDate.Text = "19/05/97", no
records are selected.
if boxNumdays.Text = "13/05/97" and boxEndDate.Text = "19/05/97", the
correct number of records are selected.

According to the documentation included with VB, the DateValue function is
aware of the international settings.  However, this does not seem to be
true.  I have printed the resulting selstr, and the dates don't appear to
have been modified to the US format as the documentation seems to indicate.
 Also, the documentation seems to indicate that the # date literal
indicators are not necessary when using the DateValue function.  This is
absolutely not true, I've tried it and it NEVER returns any records under
any circumstances.  (This is in the on-line help, search under WHERE)

I'm hoping someone may have an insight into this problem.

Thank you!

Jennifer Bavar



Sun, 07 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL

Try something like:
  selstr = selstr & " AND dataAnalise > #" & Format(CDate(boxNumdays.Text),
"yyyy-mm-dd") & "#"

Where the "yyyy-mm-dd" matches whatever format your SQL-Server Expects...

HTH...

------------------
Dag Sunde
Softwarer Engineer
Orion Systems As
Oslo, Norway

Quote:
>Hi everyone,

>I'm writing an app where I need to select a group of records from a
>recordset that fall between two dates.  The user enters the dates into
text
>boxes on a form, and I build a SQL select statement including the dates.
>Here is the code:

>selstr = "SELECT * FROM Anal_hist WHERE equip_id = " &
>dataEquipment.Recordset!equip_id
>selstr = selstr & " AND dataAnalise > #" & DateValue(boxNumdays.Text) &
"#"
>selstr = selstr & " AND dataAnalise <= #" & DateValue(boxEndDate.Text) &
>"#;"
>dataAnalhist.RecordSource = selstr
>dataAnalhist.Refresh

>I am using VB 4.0 32 bit under NT 4.0, US International keyboard layout,
>and short date format "dd/mm/yy" .  Here is the problem:  when the dates
>are "ambiguous", the SELECT returns no records.  For example:
>if boxNumdays.Text = "12/05/97" and boxEndDate.Text = "19/05/97", no
>records are selected.
>if boxNumdays.Text = "13/05/97" and boxEndDate.Text = "19/05/97", the
>correct number of records are selected.

>According to the documentation included with VB, the DateValue function
is
>aware of the international settings.  However, this does not seem to be
>true.  I have printed the resulting selstr, and the dates don't appear to
>have been modified to the US format as the documentation seems to
indicate.
> Also, the documentation seems to indicate that the # date literal
>indicators are not necessary when using the DateValue function.  This is
>absolutely not true, I've tried it and it NEVER returns any records under
>any circumstances.  (This is in the on-line help, search under WHERE)

>I'm hoping someone may have an insight into this problem.

>Thank you!

>Jennifer Bavar



Sun, 07 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL



Quote:
> Hi everyone,

> I'm writing an app where I need to select a group of records from a
> recordset that fall between two dates.  The user enters the dates into
text
> boxes on a form, and I build a SQL select statement including the dates.
> Here is the code:

> selstr = "SELECT * FROM Anal_hist WHERE equip_id = " &
> dataEquipment.Recordset!equip_id
> selstr = selstr & " AND dataAnalise > #" & DateValue(boxNumdays.Text) &
"#"
> selstr = selstr & " AND dataAnalise <= #" & DateValue(boxEndDate.Text) &
> "#;"
> dataAnalhist.RecordSource = selstr
> dataAnalhist.Refresh

> true.  I have printed the resulting selstr, and the dates don't appear to
> have been modified to the US format as the documentation seems to

indicate.

Quote:
> I'm hoping someone may have an insight into this problem.

> Thank you!

> Jennifer Bavar

I know, you will not like this code, but it should work (I hope this is not
the only solution :):

dim s as string    
s = Format(Text1.Text, "mm") + "/" + Format(Text1.Text, "dd") + "/" +
Format(Text1.Text, "yy")

:)))))) Even those slashes must be outside of format functions, because
they would be replaced depending on locales. I think you should add #
before and

BTW you cannot use datavalue function, because it uses locale to format the
date.
Format accepts locale format, but if it cannot interpret it as locale, it
also tries US format: e.g. we use dd/mm/yy format, but if I enter 12/24/99
it interprets as the 24th Dec 1999

Ivan.
--
---------------------------------------------------------
  Ivan Lalis
  Department of Computer Science
  Slovak Technical University, Bratislava, Slovakia

  http://www.dcs.elf.stuba.sk/~lalis/
  phone: +421-7-791 384
  S+++ Fa1 M++++ K-- H+ AT-- -SY FO--- D- P- Tz-
---------------------------------------------------------



Mon, 08 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL

Try this below:

------------------------------------------------------------------------
Public Function UniDate(Byval vDate As Variant) As String

On Error Resume Next
UniDate = "#" & Month(vDate) & "/" & Day(vDate) & "/" & Year(vDate) &
"#"

End Function
------------------------------------------------------------------------
In Your SQL query, just reference to the function above:

selstr = "SELECT * FROM Anal_hist WHERE equip_id = " &
dataEquipment.Recordset!equip_id
selstr = selstr & " AND dataAnalise > " & UniDate(boxNumdays.Text)
selstr = selstr & " AND dataAnalise <= " & UniDate(boxEndDate.Text) &
";"

I personally have databases with Swedish/Finnish locale and the above
function always works. It seems that no matter what locale You use,
the English/American format always works in Microsoft Access databases!

Peter Larsson



Tue, 09 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL

Someone recommended a technique here a few months ago which I have been
using since then, and have had _no_ more trouble of the kind you mention
- I used to have endless difficulty!

The answer is to define yourself a global string constant with a full
date and time specification - something like

   Public Const DATEFMT As String = "dd mmm yy ttttt"

and then use it to format _every_ date which goes into an SQL query,
thus:

  sSQL="SELECT * FROM Foo WHERE dDat=#" & format(dMyDat,DATEFMT) & "#"

It might look a bit kludgy but believe me it works all the time.  If
whoever it was that posted the idea recognises it and sends me EMail
I'll acknowledge her/him!



Quote:
>I am using VB 4.0 32 bit under NT 4.0, US International keyboard layout,
>and short date format "dd/mm/yy" .  Here is the problem:  when the dates
>are "ambiguous", the SELECT returns no records.  For example:
>if boxNumdays.Text = "12/05/97" and boxEndDate.Text = "19/05/97", no
>records are selected.
>if boxNumdays.Text = "13/05/97" and boxEndDate.Text = "19/05/97", the
>correct number of records are selected.


Manchester, England


Thu, 11 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL



Quote:
> The answer is to define yourself a global string constant with a full
> date and time specification - something like
>    Public Const DATEFMT As String = "dd mmm yy ttttt"
> and then use it to format _every_ date which goes into an SQL query,

cut...

Quote:
> It might look a bit kludgy but believe me it works all the time.  If
> whoever it was that posted the idea recognises it and sends me EMail
> I'll acknowledge her/him!

Hello Henry

Possibly me, I've posted this approach many times. Maybe I'm just
sick and tired of seeing the #mm/dd/yy# approach as often quoted
as being the best or even only approach.

I use the "dd mmm yyyy ..." above, or rather my customers, in
several countries across Europe and so far so good. :)

Pleased it works for you.
--
ernest     at Noel Systems, near Kingston-upon-Thames (UK)
           (if replying by email, remove the * from address)



Fri, 12 Nov 1999 03:00:00 GMT  
 Non-US date formats and SQL

hi,
I had the same problem...
and solved it like this FIRST I NEVER USE THE DATE DATATYPE !
it's much easyer to find dates with SQL when you use a NUMBER as
Datatype in your databaseTable and enter the date as showed.

date= 20/03/97
use the date like this :        19970320 (year,month,day)

you just have to write a little dateconverter which set the date in
this format in your database and all your problems are solved.

Hope this will help (it will) ....
greetings Koen Amant

Quote:

>Hi everyone,
>I'm writing an app where I need to select a group of records from a
>recordset that fall between two dates.  The user enters the dates into text
>boxes on a form, and I build a SQL select statement including the dates.
>Here is the code:
>selstr = "SELECT * FROM Anal_hist WHERE equip_id = " &
>dataEquipment.Recordset!equip_id
>selstr = selstr & " AND dataAnalise > #" & DateValue(boxNumdays.Text) & "#"
>selstr = selstr & " AND dataAnalise <= #" & DateValue(boxEndDate.Text) &
>"#;"
>dataAnalhist.RecordSource = selstr
>dataAnalhist.Refresh
>I am using VB 4.0 32 bit under NT 4.0, US International keyboard layout,
>and short date format "dd/mm/yy" .  Here is the problem:  when the dates
>are "ambiguous", the SELECT returns no records.  For example:
>if boxNumdays.Text = "12/05/97" and boxEndDate.Text = "19/05/97", no
>records are selected.
>if boxNumdays.Text = "13/05/97" and boxEndDate.Text = "19/05/97", the
>correct number of records are selected.
>According to the documentation included with VB, the DateValue function is
>aware of the international settings.  However, this does not seem to be
>true.  I have printed the resulting selstr, and the dates don't appear to
>have been modified to the US format as the documentation seems to indicate.
> Also, the documentation seems to indicate that the # date literal
>indicators are not necessary when using the DateValue function.  This is
>absolutely not true, I've tried it and it NEVER returns any records under
>any circumstances.  (This is in the on-line help, search under WHERE)
>I'm hoping someone may have an insight into this problem.
>Thank you!
>Jennifer Bavar



Sat, 13 Nov 1999 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Non-US date formats - coping with

2. is date() accepts both UK and US date formats

3. Filtering ADO Recordset on Date Field using NON-US Locale

4. Date Reverts Back To US Format When Table Updated

5. How to convert US dates to Heb format

6. US vs UK Date format

7. Switching between US and Uk date formats

8. Using non-American date formats

9. UK to US date converstation for SQL

10. Thoughts on US/UK Date formats with VB/SQL Server/T-SQL - any comments?

11. Date formatting in a SQL statement

12. SQL group by date when field has long format

 

 
Powered by phpBB® Forum Software