|
 |
asp_databases thread: Dates confusion
Message #1 by Nikos <pappas@c...> on Sat, 18 Jan 2003 05:22:13 +0200
|
|
Hi
Please this is driving me crazy
Forgive my English as it is not the best you can find
and my limited knowledge as well.
My company uses custom dates for the 52 weeks of the year
I have build a VB project that in some point sets the dates for the weeks.
If someone gives the first day of the year a routine adds a week to the
first date
and with a loop creates an insert sql statement for the 52 weeks.
the problem is that only If I format the dates mm/dd/yyyy this can be done
correctly
other ways the days and months in the table are mixed
The computers regional settings dd/mm/yyyy.
I use an msgbox to check the insert statement and everything looks correct
there
but the values inserted in the database are different
To give you an example
first day for the company's year 5th of january 2003
the msgbox ( my statement )
05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc .......
That's what you see in the table
01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc .......
can anyone explain this?
The weeks_years_T table has a field for the year as long and 52 fields
w1,w2,.......w52 as date/time for the weeks.
I tried to format the fields in the database short date or dd/mm/yyyy or
other formats with no luck.
I will be happy to send the database to anyone interested to test this.
Thank you in advance
Best regards
Nikos
theres the code
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub SETWEEKS()
Dim connectstring As String
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=test.mdb;Persist Security Info=False"
On Error GoTo err_H
Dim firstWeeksDay, newdate As Date
firstWeeksDay = InputBox("Please give a date " & vbCrLf & _
"as first weeks first day")
Dim insertweekssql$
insertweekssql = "INSERT INTO weeks_years_T"
insertweekssql = insertweekssql & "
([year],w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27,w28,w29,w30,w31,w32,w33,w
34,w35,w36,w37,w38,w39,w40,w41,w42,w43,w44,w45,w46,w47,w48,w49,w50,w51,w52)"
insertweekssql = insertweekssql & " VALUES"
insertweekssql = insertweekssql & " (" & Year(firstWeeksDay) & ","
insertweekssql = insertweekssql & " #" & firstWeeksDay & "#" & ","
newdate = firstWeeksDay
For i = 0 To 49
newdate = DateAdd("ww", 1, newdate)
insertweekssql = insertweekssql & " #" & newdate & "#" & ","
Next
newdate = DateAdd("ww", 1, newdate)
insertweekssql = insertweekssql & " #" & newdate & "#)"
MsgBox insertweekssql
Set conn = New ADODB.Connection
conn.Open connectstring
conn.Execute insertweekssql
conn.Close: Set conn = Nothing
Exit Sub
err_H:
MsgBox Err.Number & Err.Description
End Sub
Private Sub Command1_Click()
SETWEEKS
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Message #2 by sashi@a... on Sat, 18 Jan 2003 05:14:56
|
|
what databas ur using?
even ur inserting in mm/dd/yy
and date is storing in dd/mm/yy
it doest matter
u can use functions like mid() and instr()
to custom to ur needs
Message #3 by "Neil Jones" <neil@c...> on Sat, 18 Jan 2003 01:29:19 -0500
|
|
Looks like from the connection string that it's an Access database.
-----Original Message-----
From: sashi@a... [mailto:sashi@a...]
Sent: Saturday, January 18, 2003 5:15 AM
To: ASP Databases
Subject: [asp_databases] Re: Dates confusion
what databas ur using?
even ur inserting in mm/dd/yy
and date is storing in dd/mm/yy
it doest matter
u can use functions like mid() and instr()
to custom to ur needs
Message #4 by "Neil Jones" <neil@c...> on Sat, 18 Jan 2003 01:40:43 -0500
|
|
The following is a quotation from the Access online help. It may be worth
checking.
"Custom formats are displayed according to the settings specified in the
Regional Settings Properties dialog box in Windows Control Panel. Custom
formats inconsistent with the settings specified in the Regional Settings
Properties dialog box are ignored."
-----Original Message-----
From: Nikos [mailto:pappas@c...]
Sent: Friday, January 17, 2003 10:22 PM
To: ASP Databases
Subject: [asp_databases] Dates confusion
Hi
Please this is driving me crazy
Forgive my English as it is not the best you can find
and my limited knowledge as well.
My company uses custom dates for the 52 weeks of the year
I have build a VB project that in some point sets the dates for the weeks.
If someone gives the first day of the year a routine adds a week to the
first date
and with a loop creates an insert sql statement for the 52 weeks.
the problem is that only If I format the dates mm/dd/yyyy this can be done
correctly
other ways the days and months in the table are mixed
The computers regional settings dd/mm/yyyy.
I use an msgbox to check the insert statement and everything looks correct
there
but the values inserted in the database are different
To give you an example
first day for the company's year 5th of january 2003
the msgbox ( my statement )
05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc
.......
That's what you see in the table
01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc
.......
can anyone explain this?
The weeks_years_T table has a field for the year as long and 52 fields
w1,w2,.......w52 as date/time for the weeks.
I tried to format the fields in the database short date or dd/mm/yyyy or
other formats with no luck.
I will be happy to send the database to anyone interested to test this.
Thank you in advance
Best regards
Nikos
theres the code
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub SETWEEKS()
Dim connectstring As String
connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=test.mdb;Persist Security Info=False"
On Error GoTo err_H
Dim firstWeeksDay, newdate As Date
firstWeeksDay = InputBox("Please give a date " & vbCrLf & _
"as first weeks first day")
Dim insertweekssql$
insertweekssql = "INSERT INTO weeks_years_T"
insertweekssql = insertweekssql & "
([year],w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w
20,w21,w22,w23,w24,w25,w26,w27,w28,w29,w30,w31,w32,w33,w34,w35,w36,w37,w38,w
39,w40,w41,w42,w43,w44,w45,w46,w47,w48,w49,w50,w51,w52)"
insertweekssql = insertweekssql & " VALUES"
insertweekssql = insertweekssql & " (" & Year(firstWeeksDay) & ","
insertweekssql = insertweekssql & " #" & firstWeeksDay & "#" & ","
newdate = firstWeeksDay
For i = 0 To 49
newdate = DateAdd("ww", 1, newdate)
insertweekssql = insertweekssql & " #" & newdate & "#" & ","
Next
newdate = DateAdd("ww", 1, newdate)
insertweekssql = insertweekssql & " #" & newdate & "#)"
MsgBox insertweekssql
Set conn = New ADODB.Connection
conn.Open connectstring
conn.Execute insertweekssql
conn.Close: Set conn = Nothing
Exit Sub
err_H:
MsgBox Err.Number & Err.Description
End Sub
Private Sub Command1_Click()
SETWEEKS
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Message #5 by Nikos <pappas@c...> on Sat, 18 Jan 2003 15:37:05 +0200
|
|
Hi
It is access but had the same with MSDE and SQL server 2000
I would like to see suggestions how to use mid() and instr()
if you can please
thanks for you help
Regards
Nikos
At 07:14 18/01/2003, you wrote:
>what databas ur using?
>even ur inserting in mm/dd/yy
>and date is storing in dd/mm/yy
>it doest matter
>u can use functions like mid() and instr()
>to custom to ur needs
Message #6 by Nikos <pappas@c...> on Sat, 18 Jan 2003 15:30:50 +0200
|
|
Thanks Neil
I've checked this but I will do again.
I don't understand
Why the sql statement (in the message box ) is correct
and the days inserted are different?
Also not all days are different some of them are some don't.
I thing the solution to my the problem is in your answer
but I cant figure it out how to handle it.
I format the days mm/dd/yyyy in the statement and it inserts the days properly
but I want to know why.
Thank you for your help
Best regards
Nikos
At 08:40 18/01/2003, you wrote:
>The following is a quotation from the Access online help. It may be worth
>checking.
>
>"Custom formats are displayed according to the settings specified in the
>Regional Settings Properties dialog box in Windows Control Panel. Custom
>formats inconsistent with the settings specified in the Regional Settings
>Properties dialog box are ignored."
>
>
>-----Original Message-----
>From: Nikos [mailto:pappas@c...]
>Sent: Friday, January 17, 2003 10:22 PM
>To: ASP Databases
>Subject: [asp_databases] Dates confusion
>
>
>Hi
>
>Please this is driving me crazy
>
>Forgive my English as it is not the best you can find
>and my limited knowledge as well.
>
>My company uses custom dates for the 52 weeks of the year
>
>I have build a VB project that in some point sets the dates for the weeks.
>If someone gives the first day of the year a routine adds a week to the
>first date
>and with a loop creates an insert sql statement for the 52 weeks.
>
>the problem is that only If I format the dates mm/dd/yyyy this can be done
>correctly
>other ways the days and months in the table are mixed
>
>The computers regional settings dd/mm/yyyy.
>
>I use an msgbox to check the insert statement and everything looks correct
>there
>but the values inserted in the database are different
>To give you an example
>first day for the company's year 5th of january 2003
>the msgbox ( my statement )
>05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc
>.......
>That's what you see in the table
>01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc
>.......
>can anyone explain this?
>
>The weeks_years_T table has a field for the year as long and 52 fields
>w1,w2,.......w52 as date/time for the weeks.
>I tried to format the fields in the database short date or dd/mm/yyyy or
>other formats with no luck.
>I will be happy to send the database to anyone interested to test this.
>
>Thank you in advance
>Best regards
>
>Nikos
>
>theres the code
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Private Sub SETWEEKS()
>Dim connectstring As String
>
>connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>"Data Source=test.mdb;Persist Security Info=False"
>On Error GoTo err_H
>
>Dim firstWeeksDay, newdate As Date
>
>firstWeeksDay = InputBox("Please give a date " & vbCrLf & _
> "as first weeks first day")
>
>
>Dim insertweekssql$
>insertweekssql = "INSERT INTO weeks_years_T"
>insertweekssql = insertweekssql & "
>([year],w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w
>20,w21,w22,w23,w24,w25,w26,w27,w28,w29,w30,w31,w32,w33,w34,w35,w36,w37,w38,w
>39,w40,w41,w42,w43,w44,w45,w46,w47,w48,w49,w50,w51,w52)"
>insertweekssql = insertweekssql & " VALUES"
>insertweekssql = insertweekssql & " (" & Year(firstWeeksDay) & ","
>insertweekssql = insertweekssql & " #" & firstWeeksDay & "#" & ","
>
>newdate = firstWeeksDay
>
>For i = 0 To 49
>newdate = DateAdd("ww", 1, newdate)
>insertweekssql = insertweekssql & " #" & newdate & "#" & ","
>Next
>
>newdate = DateAdd("ww", 1, newdate)
>insertweekssql = insertweekssql & " #" & newdate & "#)"
>
>MsgBox insertweekssql
>
>Set conn = New ADODB.Connection
>conn.Open connectstring
>
>conn.Execute insertweekssql
>
>conn.Close: Set conn = Nothing
>Exit Sub
>err_H:
>MsgBox Err.Number & Err.Description
>End Sub
>
>Private Sub Command1_Click()
>SETWEEKS
>End Sub
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
>
Message #7 by Nikos <pappas@c...> on Sat, 18 Jan 2003 15:49:51 +0200
|
|
=C7=E9
Neil
the code modified like this is working perfect
using other supported formats by the system
not
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Dim insertweekssql$
insertweekssql =3D "INSERT INTO weeks_years_T"
insertweekssql =3D insertweekssql & "
([year],w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w
20,w21,w22,w23,w24,w25,w26,w27,w28,w29,w30,w31,w32,w33,w34,w35,w36,w37,w38,w
39,w40,w41,w42,w43,w44,w45,w46,w47,w48,w49,w50,w51,w52)"
insertweekssql =3D insertweekssql & " VALUES"
insertweekssql =3D insertweekssql & " (" & Year(firstWeeksDay) & ","
insertweekssql =3D insertweekssql & " #" & Format(firstWeeksDay,
"mm/dd/yyyy") & "#" & ","
newdate =3D firstWeeksDay
For i =3D 0 To 49
newdate =3D DateAdd("ww", 1, newdate)
insertweekssql =3D insertweekssql & " #" & Format(newdate, "mm/dd/yyyy") &
"#" & ","
Next
newdate =3D Format(DateAdd("ww", 1, newdate), "mm/dd/yyyy")
insertweekssql =3D insertweekssql & " #" & Format(newdate, "mm/dd/yyyy") &
"#)"
MsgBox insertweekssql
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
At 08:40 18/01/2003, you wrote:
>The following is a quotation from the Access online help. It may be worth
>checking.
>
>"Custom formats are displayed according to the settings specified in the
>Regional Settings Properties dialog box in Windows Control Panel. Custom
>formats inconsistent with the settings specified in the Regional Settings
>Properties dialog box are ignored."
>
>
>-----Original Message-----
>From: Nikos [mailto:pappas@c...]
>Sent: Friday, January 17, 2003 10:22 PM
>To: ASP Databases
>Subject: [asp_databases] Dates confusion
>
>
>Hi
>
>Please this is driving me crazy
>
>Forgive my English as it is not the best you can find
>and my limited knowledge as well.
>
>My company uses custom dates for the 52 weeks of the year
>
>I have build a VB project that in some point sets the dates for the weeks.
>If someone gives the first day of the year a routine adds a week to the
>first date
>and with a loop creates an insert sql statement for the 52 weeks.
>
>the problem is that only If I format the dates mm/dd/yyyy this can be done
>correctly
>other ways the days and months in the table are mixed
>
>The computers regional settings dd/mm/yyyy.
>
>I use an msgbox to check the insert statement and everything looks correct
>there
>but the values inserted in the database are different
>To give you an example
>first day for the company's year 5th of january 2003
>the msgbox ( my statement )
>05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc
>.......
>That's what you see in the table
>01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc
>.......
>can anyone explain this?
>
>The weeks_years_T table has a field for the year as long and 52 fields
>w1,w2,.......w52 as date/time for the weeks.
>I tried to format the fields in the database short date or dd/mm/yyyy or
>other formats with no luck.
>I will be happy to send the database to anyone interested to test this.
>
>Thank you in advance
>Best regards
>
>Nikos
>
>theres the code
>
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+
>Private Sub SETWEEKS()
>Dim connectstring As String
>
>connectstring =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
>"Data Source=3Dtest.mdb;Persist Security Info=3DFalse"
>On Error GoTo err_H
>
>Dim firstWeeksDay, newdate As Date
>
>firstWeeksDay =3D InputBox("Please give a date " & vbCrLf & _
> "as first weeks first day")
>
>
>Dim insertweekssql$
>insertweekssql =3D "INSERT INTO weeks_years_T"
>insertweekssql =3D insertweekssql & "
>([year],w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,
w
>20,w21,w22,w23,w24,w25,w26,w27,w28,w29,w30,w31,w32,w33,w34,w35,w36,w37,w38,
w
>39,w40,w41,w42,w43,w44,w45,w46,w47,w48,w49,w50,w51,w52)"
>insertweekssql =3D insertweekssql & " VALUES"
>insertweekssql =3D insertweekssql & " (" & Year(firstWeeksDay) & ","
>insertweekssql =3D insertweekssql & " #" & firstWeeksDay & "#" & ","
>
>newdate =3D firstWeeksDay
>
>For i =3D 0 To 49
>newdate =3D DateAdd("ww", 1, newdate)
>insertweekssql =3D insertweekssql & " #" & newdate & "#" & ","
>Next
>
>newdate =3D DateAdd("ww", 1, newdate)
>insertweekssql =3D insertweekssql & " #" & newdate & "#)"
>
>MsgBox insertweekssql
>
>Set conn =3D New ADODB.Connection
>conn.Open connectstring
>
>conn.Execute insertweekssql
>
>conn.Close: Set conn =3D Nothing
>Exit Sub
>err_H:
>MsgBox Err.Number & Err.Description
>End Sub
>
>Private Sub Command1_Click()
>SETWEEKS
>End Sub
>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+
>
>
>
>
>
Message #8 by Nikos <pappas@c...> on Sat, 18 Jan 2003 16:04:01 +0200
|
|
Sorry for my previous mail
Formating
mm/dd/yyyy or yyyy/mm/dd the days are inserted fine
formating dd/mm/yyyy which is the computers default
or not formating at all gets messy.
I have the same problem with SELECT statements
if i use date1 between date2 I have to format date1 and date2
mm/dd/yyyy or yyyy/mm/dd to fet the correct period
Thanks again for your time and help
All the best
Nikos
At 08:40 18/01/2003, you wrote:
>The following is a quotation from the Access online help. It may be worth
>checking.
>
>"Custom formats are displayed according to the settings specified in the
>Regional Settings Properties dialog box in Windows Control Panel. Custom
>formats inconsistent with the settings specified in the Regional Settings
>Properties dialog box are ignored."
>
>
>-----Original Message-----
>From: Nikos [mailto:pappas@c...]
>Sent: Friday, January 17, 2003 10:22 PM
>To: ASP Databases
>Subject: [asp_databases] Dates confusion
>
>
>Hi
>
>Please this is driving me crazy
>
>Forgive my English as it is not the best you can find
>and my limited knowledge as well.
>
>My company uses custom dates for the 52 weeks of the year
>
>I have build a VB project that in some point sets the dates for the weeks.
>If someone gives the first day of the year a routine adds a week to the
>first date
>and with a loop creates an insert sql statement for the 52 weeks.
>
>the problem is that only If I format the dates mm/dd/yyyy this can be done
>correctly
>other ways the days and months in the table are mixed
>
>The computers regional settings dd/mm/yyyy.
>
>I use an msgbox to check the insert statement and everything looks correct
>there
>but the values inserted in the database are different
>To give you an example
>first day for the company's year 5th of january 2003
>the msgbox ( my statement )
>05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc
>.......
>That's what you see in the table
>01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc
>.......
>can anyone explain this?
>
>The weeks_years_T table has a field for the year as long and 52 fields
>w1,w2,.......w52 as date/time for the weeks.
>I tried to format the fields in the database short date or dd/mm/yyyy or
>other formats with no luck.
>I will be happy to send the database to anyone interested to test this.
>
>Thank you in advance
>Best regards
>
>Nikos
>
>theres the code
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Private Sub SETWEEKS()
>Dim connectstring As String
>
>connectstring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>"Data Source=test.mdb;Persist Security Info=False"
>On Error GoTo err_H
>
>Dim firstWeeksDay, newdate As Date
>
>firstWeeksDay = InputBox("Please give a date " & vbCrLf & _
> "as first weeks first day")
>
>
>Dim insertweekssql$
>insertweekssql = "INSERT INTO weeks_years_T"
>insertweekssql = insertweekssql & "
>([year],w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w
>20,w21,w22,w23,w24,w25,w26,w27,w28,w29,w30,w31,w32,w33,w34,w35,w36,w37,w38,w
>39,w40,w41,w42,w43,w44,w45,w46,w47,w48,w49,w50,w51,w52)"
>insertweekssql = insertweekssql & " VALUES"
>insertweekssql = insertweekssql & " (" & Year(firstWeeksDay) & ","
>insertweekssql = insertweekssql & " #" & firstWeeksDay & "#" & ","
>
>newdate = firstWeeksDay
>
>For i = 0 To 49
>newdate = DateAdd("ww", 1, newdate)
>insertweekssql = insertweekssql & " #" & newdate & "#" & ","
>Next
>
>newdate = DateAdd("ww", 1, newdate)
>insertweekssql = insertweekssql & " #" & newdate & "#)"
>
>MsgBox insertweekssql
>
>Set conn = New ADODB.Connection
>conn.Open connectstring
>
>conn.Execute insertweekssql
>
>conn.Close: Set conn = Nothing
>Exit Sub
>err_H:
>MsgBox Err.Number & Err.Description
>End Sub
>
>Private Sub Command1_Click()
>SETWEEKS
>End Sub
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
>
Message #9 by "Ken Schaefer" <ken@a...> on Mon, 20 Jan 2003 14:06:55 +1100
|
|
www.adopenstatic.com/faq/dateswithaccess.asp
Use ISO-style dates (yyyy/mm/dd or yyyy-mm-dd or yyyymmdd) for
inserting/updating/querying and you will be fine.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Nikos" <pappas@c...>
Subject: [asp_databases] RE: Dates confusion
: Sorry for my previous mail
: Formating
: mm/dd/yyyy or yyyy/mm/dd the days are inserted fine
: formating dd/mm/yyyy which is the computers default
: or not formating at all gets messy.
:
: I have the same problem with SELECT statements
: if i use date1 between date2 I have to format date1 and date2
: mm/dd/yyyy or yyyy/mm/dd to fet the correct period
:
: Thanks again for your time and help
:
: All the best
: Nikos
: At 08:40 18/01/2003, you wrote:
: >The following is a quotation from the Access online help. It may be
worth
: >checking.
: >
: >"Custom formats are displayed according to the settings specified in the
: >Regional Settings Properties dialog box in Windows Control Panel. Custom
: >formats inconsistent with the settings specified in the Regional Settings
: >Properties dialog box are ignored."
: >
: >
: >-----Original Message-----
: >From: Nikos [mailto:pappas@c...]
: >Sent: Friday, January 17, 2003 10:22 PM
: >To: ASP Databases
: >Subject: [asp_databases] Dates confusion
: >
: >
: >Hi
: >
: >Please this is driving me crazy
: >
: >Forgive my English as it is not the best you can find
: >and my limited knowledge as well.
: >
: >My company uses custom dates for the 52 weeks of the year
: >
: >I have build a VB project that in some point sets the dates for the
weeks.
: >If someone gives the first day of the year a routine adds a week to the
: >first date
: >and with a loop creates an insert sql statement for the 52 weeks.
: >
: >the problem is that only If I format the dates mm/dd/yyyy this can be
done
: >correctly
: >other ways the days and months in the table are mixed
: >
: >The computers regional settings dd/mm/yyyy.
: >
: >I use an msgbox to check the insert statement and everything looks
correct
: >there
: >but the values inserted in the database are different
: >To give you an example
: >first day for the company's year 5th of january 2003
: >the msgbox ( my statement )
: >05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc
: >.......
: >That's what you see in the table
: >01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc
: >.......
: >can anyone explain this?
: >
: >The weeks_years_T table has a field for the year as long and 52 fields
: >w1,w2,.......w52 as date/time for the weeks.
: >I tried to format the fields in the database short date or dd/mm/yyyy or
: >other formats with no luck.
: >I will be happy to send the database to anyone interested to test this.
: >
: >Thank you in advance
: >Best regards
: >
: >Nikos
: >
: >theres the code
Message #10 by Nikos <pappas@c...> on Mon, 20 Jan 2003 10:45:50 +0200
|
|
Thank you Ken
adopenstatic is a great help but I didn't
check for this.
I will check it out
Best regards
Nikos
At 05:06 20/01/2003, you wrote:
>www.adopenstatic.com/faq/dateswithaccess.asp
>
>Use ISO-style dates (yyyy/mm/dd or yyyy-mm-dd or yyyymmdd) for
>inserting/updating/querying and you will be fine.
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Nikos" <pappas@c...>
>Subject: [asp_databases] RE: Dates confusion
>
>
>: Sorry for my previous mail
>: Formating
>: mm/dd/yyyy or yyyy/mm/dd the days are inserted fine
>: formating dd/mm/yyyy which is the computers default
>: or not formating at all gets messy.
>:
>: I have the same problem with SELECT statements
>: if i use date1 between date2 I have to format date1 and date2
>: mm/dd/yyyy or yyyy/mm/dd to fet the correct period
>:
>: Thanks again for your time and help
>:
>: All the best
>: Nikos
>: At 08:40 18/01/2003, you wrote:
>: >The following is a quotation from the Access online help. It may be
>worth
>: >checking.
>: >
>: >"Custom formats are displayed according to the settings specified in the
>: >Regional Settings Properties dialog box in Windows Control Panel. Custom
>: >formats inconsistent with the settings specified in the Regional Settings
>: >Properties dialog box are ignored."
>: >
>: >
>: >-----Original Message-----
>: >From: Nikos [mailto:pappas@c...]
>: >Sent: Friday, January 17, 2003 10:22 PM
>: >To: ASP Databases
>: >Subject: [asp_databases] Dates confusion
>: >
>: >
>: >Hi
>: >
>: >Please this is driving me crazy
>: >
>: >Forgive my English as it is not the best you can find
>: >and my limited knowledge as well.
>: >
>: >My company uses custom dates for the 52 weeks of the year
>: >
>: >I have build a VB project that in some point sets the dates for the
>weeks.
>: >If someone gives the first day of the year a routine adds a week to the
>: >first date
>: >and with a loop creates an insert sql statement for the 52 weeks.
>: >
>: >the problem is that only If I format the dates mm/dd/yyyy this can be
>done
>: >correctly
>: >other ways the days and months in the table are mixed
>: >
>: >The computers regional settings dd/mm/yyyy.
>: >
>: >I use an msgbox to check the insert statement and everything looks
>correct
>: >there
>: >but the values inserted in the database are different
>: >To give you an example
>: >first day for the company's year 5th of january 2003
>: >the msgbox ( my statement )
>: >05/01/2003 12/01/2003 19/01/2003 26/01/2003 02/02/2003 09/02/2003 etc
>: >.......
>: >That's what you see in the table
>: >01/05/2003 01/12/2003 19/01/2003 26/01/2003 02/02/2003 02/09/2003 etc
>: >.......
>: >can anyone explain this?
>: >
>: >The weeks_years_T table has a field for the year as long and 52 fields
>: >w1,w2,.......w52 as date/time for the weeks.
>: >I tried to format the fields in the database short date or dd/mm/yyyy or
>: >other formats with no luck.
>: >I will be happy to send the database to anyone interested to test this.
>: >
>: >Thank you in advance
>: >Best regards
>: >
>: >Nikos
>: >
>: >theres the code
>
>
|
|
|
View Cart
