SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot Octal's Avatar
    Join Date
    Feb 2003
    Location
    UK
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Server 6.5 date/time woes

    Hello everyone
    I am having a little trouble with an events system written in PHP and using MS SQL Server 6.5.

    In the database table is a field for time (of event). I can insert a time no problem but when I retrieve an entry and try to parse it via php's date() function it throws an error because MS SQL adds a default date of January 1st 1901. Any suggestions for a fix please?

    • Is there a way of changing MS SQL Server's default date?
    • Is there a way to get PHP to automatically add a date before submitting to the database?


    T.I.A
    Octal - All your base-8 belong to us
    "Knowing is not enough, we must apply.
    Willing is not enough, we must do." - Bruce Lee

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "Is there a way of changing MS SQL Server's default date?"

    no

    what you want to do is

    1. understand that in microsoft sql server, every datetime value includes both a date and a time

    2. combine your event date and event time columns

    of course, if your app is written with code that assumes there are two separate fields for this, then #2 may not be possible

    in that case, i suggest storing the same value into both the event date and event time columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Octal's Avatar
    Join Date
    Feb 2003
    Location
    UK
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy

    1) Yes I was/am aware of this.
    2) Makes sense. Coding shouldn't be too difficult to alter to reflect the change in database structure either.

    Which leads to another problem. SQL Server is only accepting dates in US format I need them to be in UK format. This is despite all relevant regional settings being set correctly.

    Edit:

    Small update, I spoke with the person who set up the database server and he says the regional settings for the server were set to UK prior to installation and SQL server would have picked this up automatically. He suggests that it is a problem with the PHP code. I don't know a thing about MS SQL Server but how can it be a coding problem? The php just grabs the data entered by the user, sticks it into a SQL statement and then executes that statement, or am I missing something...


    T.I.A
    Octal - All your base-8 belong to us
    "Knowing is not enough, we must apply.
    Willing is not enough, we must do." - Bruce Lee

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the only setting i am familiar with in sql server for date formats is SET DATEFORMAT

    you can use this right in your query

    here's some testing to show you how:
    Code:
    create table testdatestrings
    ( adate  datetime not null );
    insert into testdatestrings values ( '2004-04-11' );
    
    set dateformat dmy
    insert into testdatestrings values ( '2004-04-11' );
    
    set dateformat mdy
    insert into testdatestrings values ( '2004-04-11' );
    
    select * from testdatestrings
    
    2004-04-11 00:00:00.000
    2004-11-04 00:00:00.000
    2004-04-11 00:00:00.000
    note that the DATEFORMAT values are MDY and DMY but you can still enter your dates with the year first!!

    if you suspect sql server is only accepting US formats,
    use SET DATEFORMAT MDY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Octal's Avatar
    Join Date
    Feb 2003
    Location
    UK
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thankyou sir.
    Octal - All your base-8 belong to us
    "Knowing is not enough, we must apply.
    Willing is not enough, we must do." - Bruce Lee


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •