Date in database

Discussion in 'ASP General' started by fniles, Jan 8, 2008.

  1. fniles

    fniles Guest

    On my machine in the office I change the computer setting to English (UK) so
    the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    This problem happens in either Access or SQL Server.
    In the database I have a table with Date/time column. The database is
    located on a machine that is set to dd/mm/yyyy also.
    When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    database as 1/7/08 instead of 7/1/08. Why is it like that and how can I make
    the database stores it as 7/1/08 ?
    Thank you.
     
    fniles, Jan 8, 2008
    #1
    1. Advertising

  2. fniles

    Saga Guest

    You can set the regional date format to anything you like, but when using SQLs to
    insert or update data, you must use the yyyy-mm-dd format for SQL Server or the
    mm/dd/yyyy format for Access.

    If using the Addnew method then assigning the date value to the field, you must use
    a date variable type:

    dim strDate as string

    strDate = date

    open recordset "rs"

    rs.addnew
    rs!DateField = strDate
    rs.update

    The above wont work!!! (for any date format other than mm/dd/yyyy)

    Change it to this:

    dim dteDate as date

    dteDate = date

    open recordset "rs"

    rs.addnew
    rs!DateField = dteDate
    rs.update

    This example is so trivial, that you can assign Date directly, but when using
    calculated dates the variable will most likely be needed.

    To build an SQL with a date:

    sSQL = "insert into MyTable (Mydate) values ("
    sSQL = sSQL & "'" & format$(date, "yyyy-mm-dd") & "')"

    For Access change that to:

    sSQL = sSQL & "'" & format$(date, "mm/dd/yyyy") & "')"

    HTH Saga
    --



    "fniles" <> wrote in message
    news:%...
    > On my machine in the office I change the computer setting to English (UK) so the date format is
    > dd/mm/yyyy instead of mm/dd/yyyy for US.
    > This problem happens in either Access or SQL Server.
    > In the database I have a table with Date/time column. The database is located on a machine that is
    > set to dd/mm/yyyy also.
    > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead
    > of 7/1/08. Why is it like that and how can I make the database stores it as 7/1/08 ?
    > Thank you.
    >
    >
     
    Saga, Jan 8, 2008
    #2
    1. Advertising

  3. Access will work fine with yyyy-mm-dd as well. However, you need to delimit
    the dates with # characters in Access, not single quotes.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Saga" <> wrote in message
    news:...
    > This example is so trivial, that you can assign Date directly, but when
    > using
    > calculated dates the variable will most likely be needed.
    >
    > To build an SQL with a date:
    >
    > sSQL = "insert into MyTable (Mydate) values ("
    > sSQL = sSQL & "'" & format$(date, "yyyy-mm-dd") & "')"
    >
    > For Access change that to:
    >
    > sSQL = sSQL & "'" & format$(date, "mm/dd/yyyy") & "')"
    >
    >
    > "fniles" <> wrote in message
    > news:%...
    >> On my machine in the office I change the computer setting to English (UK)
    >> so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    >> This problem happens in either Access or SQL Server.
    >> In the database I have a table with Date/time column. The database is
    >> located on a machine that is set to dd/mm/yyyy also.
    >> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    >> database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
    >> make the database stores it as 7/1/08 ?
    >> Thank you.
     
    Douglas J. Steele, Jan 8, 2008
    #3
  4. fniles

    Saga Guest

    Thanks for catching that :) I neglected to swap out the quotes for the # char.

    Noted on the yyyy-mm-dd format for Access.

    Saga
    --


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:...
    > Access will work fine with yyyy-mm-dd as well. However, you need to delimit the dates with #
    > characters in Access, not single quotes.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Saga" <> wrote in message news:...
    >> This example is so trivial, that you can assign Date directly, but when using
    >> calculated dates the variable will most likely be needed.
    >>
    >> To build an SQL with a date:
    >>
    >> sSQL = "insert into MyTable (Mydate) values ("
    >> sSQL = sSQL & "'" & format$(date, "yyyy-mm-dd") & "')"
    >>
    >> For Access change that to:
    >>
    >> sSQL = sSQL & "'" & format$(date, "mm/dd/yyyy") & "')"
    >>
    >>
    >> "fniles" <> wrote in message
    >> news:%...
    >>> On my machine in the office I change the computer setting to English (UK) so the date format is
    >>> dd/mm/yyyy instead of mm/dd/yyyy for US.
    >>> This problem happens in either Access or SQL Server.
    >>> In the database I have a table with Date/time column. The database is located on a machine that
    >>> is set to dd/mm/yyyy also.
    >>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead
    >>> of 7/1/08. Why is it like that and how can I make the database stores it as 7/1/08 ?
    >>> Thank you.

    >
    >
     
    Saga, Jan 8, 2008
    #4
  5. fniles

    MikeD Guest

    "fniles" <> wrote in message
    news:%...
    > On my machine in the office I change the computer setting to English (UK)
    > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    > This problem happens in either Access or SQL Server.
    > In the database I have a table with Date/time column. The database is
    > located on a machine that is set to dd/mm/yyyy also.
    > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
    > make the database stores it as 7/1/08 ?


    If the data type of the column is truly one of the various "date" data
    types, the format of the date is irrelevant. Don't worry about it.

    However, if the data type is actually text or characters, then you've got a
    huge problem.


    --
    Mike
    Microsoft MVP Visual Basic
     
    MikeD, Jan 8, 2008
    #5
  6. fniles

    Guest

    Thank you everybody.
    It turns out that in VB it works fine, but it does not work in ASP.
    The data type of the column is truly a "date/time" column in Access
    and "Datetime" column in SQL Server.

    I do need the date to be stored in the correct format in the database,
    because in my ASP program I do the following:
    sDay = day(d)
    sMonth = month(d)
    sYear = year(d)
    If it is not stored correctly in the database, the above functions do
    not return the correct values.

    It seems to work when I do the following (it stores 8/1/08 in the
    database)
    ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
    ID = 1"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = dbConnection
    rs.open ssql

    But when I do the following, it stores 1/8/08 in the database:
    ssql = "select * from myTBL where ID = 1"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = dbConnection
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Source = sSql
    rs.Open
    rs("colDate") = cdate(#8/1/08#) '--> got the same result when i do
    rs("colDate") = cdate("8/1/08")
    rs.Update
    rs.close
    set rs = nothing

    I could use the "update" command on the 1st method, but I would like
    to use the 2nd method if possible. Is it possible to make the 2nd
    method above work ?
    Thank you.


    On Jan 8, 5:41 pm, "MikeD" <> wrote:
    > "fniles" <> wrote in message
    >
    > news:%...
    >
    > > On my machine in the office I change the computer setting to English (UK)
    > > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    > > This problem happens in either Access or SQL Server.
    > > In the database I have a table with Date/time column. The database is
    > > located on a machine that is set to dd/mm/yyyy also.
    > > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    > > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
    > > make the database stores it as 7/1/08 ?

    >
    > If the data type of the column is truly one of the various "date" data
    > types, the format of the date is irrelevant. Don't worry about it.
    >
    > However, if the data type is actually text or characters, then you've got a
    > huge problem.
    >
    > --
    > Mike
    > Microsoft MVP Visual Basic
     
    , Jan 9, 2008
    #6
  7. fniles

    SMussler Guest

    >>> I do need the date to be stored in the correct format in the database,

    The database has it's own way of storing dates - typically in a Julian
    value.
    How you want to display it is done via formating.

    Take a look at this link:
    http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

    I myself work mainly with Oracle, which I think does something similar and
    occasionally with Access.

    Not sure about Access, but a google search could get you the details I'm
    sure.
    But what the Database engine itself is storing is irrelevant.
    What's important is getting the data entered properly and displayed as
    needed.

    Steve Mussler
     
    SMussler, Jan 9, 2008
    #7
  8. fniles

    Saga Guest

    Inline

    Saga

    --


    <> wrote in message
    news:...
    Thank you everybody.
    It turns out that in VB it works fine, but it does not work in ASP.
    The data type of the column is truly a "date/time" column in Access
    and "Datetime" column in SQL Server.

    I do need the date to be stored in the correct format in the database,
    because in my ASP program I do the following:
    sDay = day(d)
    sMonth = month(d)
    sYear = year(d)
    If it is not stored correctly in the database, the above functions do
    not return the correct values.

    It seems to work when I do the following (it stores 8/1/08 in the
    database)
    ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
    ID = 1"

    ***Reply***
    That format statement just does not look right. Use 4 digits for year
    and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
    ***

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = dbConnection
    rs.open ssql

    But when I do the following, it stores 1/8/08 in the database:
    ssql = "select * from myTBL where ID = 1"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = dbConnection
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Source = sSql
    rs.Open
    rs("colDate") = cdate(#8/1/08#)
    '--> got the same result when i do rs("colDate") = cdate("8/1/08")

    ***Reply***
    When specifying a literal date (#8/1/08#) VB always expects mm/dd/yyyy
    format. So the above will be interpreted as Aug 1st, 2008. The CDate(##)
    statement above is useless since you are in effect converting a date into
    a date.

    On the other hand, cdate("8/1/08") is converting the string "8/1/08"into a
    date which is a step in the right direction, but it is still not 100% "safe":

    "CDate recognizes date formats according to the locale setting of your
    system." - MSDN Library

    I did the following in the immediate window:

    ? cdate("8/1/08")
    08/01/2008 (2nd) 8th of Jan
    8/1/2008 (1st) Aug 1st

    I set my regional settings to English US for the 1st try. I then set the
    regional settings to English UK for the 2nd test. Note how the "same" date
    was displayed, but is intepreted differently depending on locale. Best to stay
    away from having dates in strings and using these to update tables or do
    further date calculations where the month and day could be ambiguous.

    I did another test (in the immediate wndow):

    ? #8/1/2008#
    8/1/2008 US setting Aug 1st 2008
    01/08/2008 UK setting 1st of Aug 2008 Same date!!!

    Note how the above literal is interpreted as the same date but when displayed
    it is done using the correct locale format. As mentioned before, when the #
    char is used to specify a date literal it is always interpreted as mm/dd/yyyy.
    ***

    rs.Update
    rs.close
    set rs = nothing

    I could use the "update" command on the 1st method, but I would like
    to use the 2nd method if possible. Is it possible to make the 2nd
    method above work ?
    Thank you.


    ***PS: I have no idea why the OP's text was not indented with the ">"
    char. It seems to have been only for this message.



    On Jan 8, 5:41 pm, "MikeD" <> wrote:
    > "fniles" <> wrote in message
    >
    > news:%...
    >
    > > On my machine in the office I change the computer setting to English (UK)
    > > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    > > This problem happens in either Access or SQL Server.
    > > In the database I have a table with Date/time column. The database is
    > > located on a machine that is set to dd/mm/yyyy also.
    > > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    > > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
    > > make the database stores it as 7/1/08 ?

    >
    > If the data type of the column is truly one of the various "date" data
    > types, the format of the date is irrelevant. Don't worry about it.
    >
    > However, if the data type is actually text or characters, then you've got a
    > huge problem.
    >
    > --
    > Mike
    > Microsoft MVP Visual Basic
     
    Saga, Jan 9, 2008
    #8
  9. fniles

    Dooza Guest

    fniles wrote:
    > On my machine in the office I change the computer setting to English (UK) so
    > the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    > This problem happens in either Access or SQL Server.
    > In the database I have a table with Date/time column. The database is
    > located on a machine that is set to dd/mm/yyyy also.
    > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I make
    > the database stores it as 7/1/08 ?
    > Thank you.


    Would setting the locale help?
    Not sure if your using VBScript, but here is something about it:
    http://www.w3schools.com/vbscript/func_setlocale.asp

    Steve
     
    Dooza, Jan 9, 2008
    #9
  10. fniles

    Saga Guest

    Although setting the locale via VB6 code -could- be a potential "fix", I humbly
    advice against it as it might make more enemies than friends. One's app
    should adapt the locale setting, NOT the locale setting to the app.

    Saga
    --



    "Dooza" <> wrote in message news:...
    > fniles wrote:
    >> On my machine in the office I change the computer setting to English (UK) so the date format is
    >> dd/mm/yyyy instead of mm/dd/yyyy for US.
    >> This problem happens in either Access or SQL Server.
    >> In the database I have a table with Date/time column. The database is located on a machine that
    >> is set to dd/mm/yyyy also.
    >> When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead
    >> of 7/1/08. Why is it like that and how can I make the database stores it as 7/1/08 ?
    >> Thank you.

    >
    > Would setting the locale help?
    > Not sure if your using VBScript, but here is something about it:
    > http://www.w3schools.com/vbscript/func_setlocale.asp
    >
    > Steve
     
    Saga, Jan 9, 2008
    #10
  11. fniles

    Guest

    Thank you everyone.

    So, both the client machine and the server where the ASP is running
    and where the database is located are set to UK setting. I inserted
    Jan 8, 08 (8/1/08) to the database, and after retrieving the value
    back from the database, I use the functions Day and Month, and they
    return wrong day and month.

    ssql = "select * from myTBL where ID = 1"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = dbConnection
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Source = sSql
    rs.Open
    rs("colDate") = cdate("8/1/08")
    rs.update
    :
    ssql = "select * from myTBL where ID = 1"
    Set rs2 = dbConnection.Execute(sSQL)
    if not (rs2.EOF) then
    date3 = rs2("DATA_DATE")
    sDay = day(date3) -->>>> RETURNS 1, where it suppose to
    return 8
    sMonth = month(date3) ->>>> RETURNS 8 (August), where it
    suppose to return 1 (January)
    end if



    On Jan 9, 10:18 am, "Saga" <> wrote:
    > Inline
    >
    > Saga
    >
    > --
    >
    > <> wrote in message
    >
    > news:...
    > Thank you everybody.
    > It turns out that in VB it works fine, but it does not work in ASP.
    > The data type of the column is truly a "date/time" column in Access
    > and "Datetime" column in SQL Server.
    >
    > I do need the date to be stored in the correct format in the database,
    > because in my ASP program I do the following:
    > sDay = day(d)
    > sMonth = month(d)
    > sYear = year(d)
    > If it is not stored correctly in the database, the above functions do
    > not return the correct values.
    >
    > It seems to work when I do the following (it stores 8/1/08 in the
    > database)
    > ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy')  where
    > ID = 1"
    >
    > ***Reply***
    > That format statement just does not look right. Use 4 digits for year
    > and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
    > ***
    >
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > rs.ActiveConnection = dbConnection
    > rs.open ssql
    >
    > But when I do the following, it stores 1/8/08 in the database:
    > ssql = "select * from myTBL where ID = 1"
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > rs.ActiveConnection = dbConnection
    > rs.CursorType = adOpenKeyset
    > rs.LockType = adLockOptimistic
    > rs.Source = sSql
    > rs.Open
    > rs("colDate") = cdate(#8/1/08#)
    > '--> got the same result when i do rs("colDate") = cdate("8/1/08")
    >
    > ***Reply***
    > When specifying a literal date (#8/1/08#) VB always expects mm/dd/yyyy
    > format. So the above will be interpreted as Aug 1st, 2008. The CDate(##)
    > statement above is useless since you are in effect converting a date into
    > a date.
    >
    > On the other hand, cdate("8/1/08") is converting the string "8/1/08"into a
    > date which is a step in the right direction, but it is still not 100% "safe":
    >
    > "CDate recognizes date formats according to the locale setting of your
    > system." - MSDN Library
    >
    > I did the following in the immediate window:
    >
    > ? cdate("8/1/08")
    > 08/01/2008  (2nd)  8th of Jan
    > 8/1/2008     (1st)   Aug 1st
    >
    > I set my regional settings to English US for the 1st try. I then set the
    > regional settings to English UK for the 2nd test. Note how the "same" date
    > was displayed, but is intepreted differently depending on locale. Best to stay
    > away from having dates in strings and using these to update tables or do
    > further date calculations where the month and day could be ambiguous.
    >
    > I did another test (in the immediate wndow):
    >
    > ? #8/1/2008#
    > 8/1/2008    US setting  Aug 1st 2008
    > 01/08/2008   UK setting  1st of Aug 2008   Same date!!!
    >
    > Note how the above literal is interpreted as the same date but when displayed
    > it is done using the correct locale format. As mentioned before, when the #
    > char is used to specify a date literal it is always interpreted as mm/dd/yyyy.
    > ***
    >
    > rs.Update
    > rs.close
    > set rs = nothing
    >
    > I could use the "update" command on the 1st method, but I would like
    > to use the 2nd method if possible. Is it possible to make the 2nd
    > method above work ?
    > Thank you.
    >
    > ***PS: I have no idea why the OP's text was not indented with the ">"
    > char. It seems to have been only for this message.
    >
    > On Jan 8, 5:41 pm, "MikeD" <> wrote:
    >
    >
    >
    > > "fniles" <> wrote in message

    >
    > >news:%...

    >
    > > > On my machine in the office I change the computer setting to English (UK)
    > > > so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US.
    > > > This problem happens in either Access or SQL Server.
    > > > In the database I have a table with Date/time column. The database is
    > > > located on a machine that is set to dd/mm/yyyy also.
    > > > When I enter date 7/1/08 (as in January 7, 2008), it stores it in the
    > > > database as 1/7/08 instead of 7/1/08. Why is it like that and how can I
    > > > make the database stores it as 7/1/08 ?

    >
    > > If the data type of the column is truly one of the various "date" data
    > > types, the format of the date is irrelevant. Don't worry about it.

    >
    > > However, if the data type is actually text or characters, then you've got a
    > > huge problem.

    >
    > > --
    > > Mike
    > > Microsoft MVP Visual Basic- Hide quoted text -

    >
    > - Show quoted text -
     
    , Jan 10, 2008
    #11
  12. <> wrote in message
    news:...
    >>

    Thank you everyone.

    So, both the client machine and the server where the ASP is running
    and where the database is located are set to UK setting. I inserted
    Jan 8, 08 (8/1/08) to the database, and after retrieving the value
    back from the database, I use the functions Day and Month, and they
    return wrong day and month.

    ssql = "select * from myTBL where ID = 1"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.ActiveConnection = dbConnection
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Source = sSql
    rs.Open
    rs("colDate") = cdate("8/1/08")
    rs.update
    :
    ssql = "select * from myTBL where ID = 1"
    Set rs2 = dbConnection.Execute(sSQL)
    if not (rs2.EOF) then
    date3 = rs2("DATA_DATE")
    sDay = day(date3) -->>>> RETURNS 1, where it suppose to
    return 8
    sMonth = month(date3) ->>>> RETURNS 8 (August), where it
    suppose to return 1 (January)
    end if
    <<


    Did you try this:-

    Dim dat : dat = CDate("8/1/08")

    Response.Write Day(dat) & "<br />"
    Response.Write Month(dat)


    What happens? As has been pointed out at least twice in this thread already
    the locale does not impact a date value, it only impacts the conversion to
    and from a string.

    However Locales in ASP can be tricky. Locale settings are configured at the
    user level. Assuming you're only accessing the website as an anonymous
    user, the user's locale settings you would be interested in would be the
    IUSR_<machinename>. Since this user is unlikely to have a profile on the
    machine the DEFAULT user profile will be used to find the locale settings.
    Once the locale settings are loaded they are cached by IIS.

    Hence it is possible to make 'changes' to the locale but not affect the
    settings in the DEFAULT profile which would have initially been set when the
    OS was installed. Its a common problem in the UK where Windows has been
    installed with the default US settings. What OS is the web site installed
    on?

    Personally I would avoid the xx/xx/[xx]xx format altogether. I tend to use
    01 Jan 2008 style. This is acceptable to a human and is parsable by
    VB(Script), Javascript and SQL Server (I would guess Access would have no
    problem with it either).


    --
    Anthony Jones - MVP ASP/ASP.NET
     
    Anthony Jones, Jan 10, 2008
    #12
  13. Please. Stop trying to force a particular format when inserting data into
    databases. Databases do not store format. They store numeric
    recpresentations of dates. Access stores date/times as Doubles, with the
    whole number portion representing the number of days since the seed date and
    the decimal portion representing the time (.0 = midnight, .5 = noon). SQL
    Server stores datetimes as paired integers, with the first integer
    containing the number of days since the seed date, and the second containing
    the number of milliseconds since midnight.

    You must remember that ASP (IIS) is not running under your user account, so
    just because _you_ have your regional settings set to UK, that does not mean
    the account that IIS is using has that setting, which defaults to US.

    Instead of cdate("8/1/08"), I suggest you use dateserial(2008,1,8).

    Explicitly format the dates you retrieve from the database instead of
    depending on the operating system to do it for you. If you want to make sure
    dates are displayed in UK format, use the Year(), Day() and Month()
    functions to build a string containing the correct format which you display
    to your users.

    Also, instead of using a recordset to maintain data, I suggest using a
    parameterized sql statement so you do not need to worry about delimiters. In
    vbscript, this would look like:

    ssql = "update table set coldate=? where ID = 1"
    dim cmd, arParms
    arParms = array(dateserial(2008,1,8))
    set cmd=createobject("adodb.command")
    set cmd.activeconnection = dbConnection
    cmd.commandetype = 1 'adCmdText
    cmd.commandtext = ssql
    cmd.execute ,arParms,128 'adExecuteNoRecords

    This should work regardless of the backend database.

    wrote:
    > Thank you everyone.
    >
    > So, both the client machine and the server where the ASP is running
    > and where the database is located are set to UK setting. I inserted
    > Jan 8, 08 (8/1/08) to the database, and after retrieving the value
    > back from the database, I use the functions Day and Month, and they
    > return wrong day and month.
    >
    > ssql = "select * from myTBL where ID = 1"
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > rs.ActiveConnection = dbConnection
    > rs.CursorType = adOpenKeyset
    > rs.LockType = adLockOptimistic
    > rs.Source = sSql
    > rs.Open
    > rs("colDate") = cdate("8/1/08")
    > rs.update
    >>

    > ssql = "select * from myTBL where ID = 1"
    > Set rs2 = dbConnection.Execute(sSQL)
    > if not (rs2.EOF) then
    > date3 = rs2("DATA_DATE")
    > sDay = day(date3) -->>>> RETURNS 1, where it suppose to
    > return 8
    > sMonth = month(date3) ->>>> RETURNS 8 (August), where it
    > suppose to return 1 (January)
    > end if
    >
    >
    >
    > On Jan 9, 10:18 am, "Saga" <> wrote:
    >> Inline
    >>
    >> Saga
    >>
    >> --
    >>
    >> <> wrote in message
    >>
    >> news:...
    >> Thank you everybody.
    >> It turns out that in VB it works fine, but it does not work in ASP.
    >> The data type of the column is truly a "date/time" column in Access
    >> and "Datetime" column in SQL Server.
    >>
    >> I do need the date to be stored in the correct format in the
    >> database, because in my ASP program I do the following:
    >> sDay = day(d)
    >> sMonth = month(d)
    >> sYear = year(d)
    >> If it is not stored correctly in the database, the above functions do
    >> not return the correct values.
    >>
    >> It seems to work when I do the following (it stores 8/1/08 in the
    >> database)
    >> ssql = "update myTBL set colDate = format('8/1/08','dd/mm/yy') where
    >> ID = 1"
    >>
    >> ***Reply***
    >> That format statement just does not look right. Use 4 digits for year
    >> and yyyy-mm-dd format. Using dd/mm/yy will cause problems.
    >> ***
    >>
    >> Set rs = Server.CreateObject("ADODB.Recordset")
    >> rs.ActiveConnection = dbConnection
    >> rs.open ssql
    >>
    >> But when I do the following, it stores 1/8/08 in the database:
    >> ssql = "select * from myTBL where ID = 1"
    >> Set rs = Server.CreateObject("ADODB.Recordset")
    >> rs.ActiveConnection = dbConnection
    >> rs.CursorType = adOpenKeyset
    >> rs.LockType = adLockOptimistic
    >> rs.Source = sSql
    >> rs.Open
    >> rs("colDate") = cdate(#8/1/08#)
    >> '--> got the same result when i do rs("colDate") = cdate("8/1/08")
    >>
    >> ***Reply***
    >> When specifying a literal date (#8/1/08#) VB always expects
    >> mm/dd/yyyy format. So the above will be interpreted as Aug 1st,
    >> 2008. The CDate(##) statement above is useless since you are in
    >> effect converting a date into
    >> a date.
    >>
    >> On the other hand, cdate("8/1/08") is converting the string
    >> "8/1/08"into a date which is a step in the right direction, but it
    >> is still not 100% "safe":
    >>
    >> "CDate recognizes date formats according to the locale setting of
    >> your system." - MSDN Library
    >>
    >> I did the following in the immediate window:
    >>
    >> ? cdate("8/1/08")
    >> 08/01/2008 (2nd) 8th of Jan
    >> 8/1/2008 (1st) Aug 1st
    >>
    >> I set my regional settings to English US for the 1st try. I then set
    >> the regional settings to English UK for the 2nd test. Note how the
    >> "same" date was displayed, but is intepreted differently depending
    >> on locale. Best to stay away from having dates in strings and using
    >> these to update tables or do further date calculations where the
    >> month and day could be ambiguous.
    >>
    >> I did another test (in the immediate wndow):
    >>
    >> ? #8/1/2008#
    >> 8/1/2008 US setting Aug 1st 2008
    >> 01/08/2008 UK setting 1st of Aug 2008 Same date!!!
    >>
    >> Note how the above literal is interpreted as the same date but when
    >> displayed it is done using the correct locale format. As mentioned
    >> before, when the # char is used to specify a date literal it is
    >> always interpreted as mm/dd/yyyy. ***
    >>
    >> rs.Update
    >> rs.close
    >> set rs = nothing
    >>
    >> I could use the "update" command on the 1st method, but I would like
    >> to use the 2nd method if possible. Is it possible to make the 2nd
    >> method above work ?
    >> Thank you.
    >>
    >> ***PS: I have no idea why the OP's text was not indented with the ">"
    >> char. It seems to have been only for this message.
    >>
    >> On Jan 8, 5:41 pm, "MikeD" <> wrote:
    >>
    >>
    >>
    >>> "fniles" <> wrote in message

    >>
    >>> news:%...

    >>
    >>>> On my machine in the office I change the computer setting to
    >>>> English (UK) so the date format is dd/mm/yyyy instead of
    >>>> mm/dd/yyyy for US.
    >>>> This problem happens in either Access or SQL Server.
    >>>> In the database I have a table with Date/time column. The database
    >>>> is located on a machine that is set to dd/mm/yyyy also.
    >>>> When I enter date 7/1/08 (as in January 7, 2008), it stores it in
    >>>> the database as 1/7/08 instead of 7/1/08. Why is it like that and
    >>>> how can I make the database stores it as 7/1/08 ?

    >>
    >>> If the data type of the column is truly one of the various "date"
    >>> data types, the format of the date is irrelevant. Don't worry about
    >>> it.

    >>
    >>> However, if the data type is actually text or characters, then
    >>> you've got a huge problem.

    >>
    >>> --
    >>> Mike
    >>> Microsoft MVP Visual Basic- Hide quoted text -

    >>
    >> - Show quoted text -


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 10, 2008
    #13
  14. Anthony Jones wrote:
    > Personally I would avoid the xx/xx/[xx]xx format altogether. I tend
    > to use 01 Jan 2008 style. This is acceptable to a human and is
    > parsable by VB(Script), Javascript and SQL Server (I would guess
    > Access would have no problem with it either).


    Actually, it would. Date literals in JetSQL must be passed in US format, or
    ISO. Also, a SQL Server instance with French settings would also have a
    problem with it.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jan 10, 2008
    #14
  15. fniles

    Bob Butler Guest

    <> wrote in message
    news:...
    > So, both the client machine and the server where the ASP is running
    > and where the database is located are set to UK setting. I inserted
    > Jan 8, 08 (8/1/08) to the database, and after retrieving the value
    > back from the database, I use the functions Day and Month, and they
    > return wrong day and month.
    > rs("colDate") = cdate("8/1/08")


    Even if you are sure that the date formats are all UK you should avod
    string-to-date conversions. Use #1/8/2008# or DateSerial(2008,1,8)

    I also always recommend not using the default names so:
    rs.fields("colDate").Value=#1/8/2008#

    > if not (rs2.EOF) then
    > date3 = rs2("DATA_DATE")


    Why are you setting "colDate" and retrieving "DATA_DATE"?
     
    Bob Butler, Jan 10, 2008
    #15
  16. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Anthony Jones wrote:
    > > Personally I would avoid the xx/xx/[xx]xx format altogether. I tend
    > > to use 01 Jan 2008 style. This is acceptable to a human and is
    > > parsable by VB(Script), Javascript and SQL Server (I would guess
    > > Access would have no problem with it either).

    >
    > Actually, it would. Date literals in JetSQL must be passed in US format,

    or
    > ISO. Also, a SQL Server instance with French settings would also have a
    > problem with it.
    >



    I haven't worked with Jet in such a long time I forget how limited it is.
    And yes my preference for dd mmm yyyy doesn't work internationally. :(

    Unfortunately Javascript doesn't parse an ISO date and the current
    implementations of XSL don't know what a date is. For me then dd mmm yyyy
    is a very compelling compromise.

    --
    Anthony Jones - MVP ASP/ASP.NET
     
    Anthony Jones, Jan 10, 2008
    #16
  17. fniles

    Evertjan. Guest

    Bob Butler wrote on 10 jan 2008 in microsoft.public.inetserver.asp.general:

    > Even if you are sure that the date formats are all UK you should avod
    > string-to-date conversions. Use #1/8/2008#


    The first of august?

    Please no, use:

    #2008/1/8#

    > or DateSerial(2008,1,8)


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jan 10, 2008
    #17
  18. fniles

    Bob Butler Guest

    "Evertjan." <> wrote in message
    news:Xns9A219E2E16350eejj99@194.109.133.242...
    > Bob Butler wrote on 10 jan 2008 in
    > microsoft.public.inetserver.asp.general:
    >
    >> Even if you are sure that the date formats are all UK you should avod
    >> string-to-date conversions. Use #1/8/2008#

    >
    > The first of august?


    No, the # format is always #mm/dd/yyyy# so it is consistent regardless of
    your locale

    > Please no, use:
    >
    > #2008/1/8#


    Enter this in VB and press enter:
    d=#2008/1/8#
     
    Bob Butler, Jan 10, 2008
    #18
  19. fniles

    Evertjan. Guest

    Bob Butler wrote on 10 jan 2008 in
    microsoft.public.inetserver.asp.general:

    > "Evertjan." <> wrote in message
    > news:Xns9A219E2E16350eejj99@194.109.133.242...
    >> Bob Butler wrote on 10 jan 2008 in
    >> microsoft.public.inetserver.asp.general:
    >>
    >>> Even if you are sure that the date formats are all UK you should
    >>> avod string-to-date conversions. Use #1/8/2008#

    >>
    >> The first of august?

    >
    > No, the # format is always #mm/dd/yyyy# so it is consistent regardless
    > of your locale
    >
    >> Please no, use:
    >>
    >> #2008/1/8#

    >
    > Enter this in VB and press enter:
    > d=#2008/1/8#


    Why?

    This NG is not about VB.


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jan 10, 2008
    #19
  20. Bob Butler wrote:
    > "Evertjan." <> wrote in message
    > news:Xns9A219E2E16350eejj99@194.109.133.242...
    >> Bob Butler wrote on 10 jan 2008 in
    >> microsoft.public.inetserver.asp.general:
    >>
    >>> Even if you are sure that the date formats are all UK you should
    >>> avod string-to-date conversions. Use #1/8/2008#

    >>
    >> The first of august?

    >
    > No, the # format is always #mm/dd/yyyy# so it is consistent
    > regardless of your locale
    >
    >> Please no, use:
    >>
    >> #2008/1/8#

    >
    > Enter this in VB and press enter:
    > d=#2008/1/8#


    I don't understand the point you're trying to make. I entered this:

    ?#2000/8/1#

    into the Immediate window in VB, pressed enter and it displayed:

    8/1/2000

    Were you trying to say that VB would reject that format?

    PS. newsgroups added back in to the crosspost - not sure why they were
    removed
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jan 10, 2008
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    606
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,276
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    519
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    724
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    407
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page