#1
  1. Lost In Real World
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2006
    Location
    Bodrum
    Posts
    683
    Rep Power
    399

    Smalldatetime format


    Hi,

    I am using SQL Server 2000 with my web site hosting.

    I have a field in database which is smalldatetime.

    When the form tries to insert date to database, it tries to enter MM/DD/YYYY.

    But what I need is to store it as DD/MM/YYYY.

    any idea?
    A wise man remembers his friends at all times; a fool, only when he has need of them
  2. #2
  3. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,652
    Rep Power
    2685
    DS, i dont think that u can change the way SQL Server stores the date.
    Laziness is my religion and Sunday is my God

    Get the Mantra!
  4. #3
  5. Lost In Real World
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2006
    Location
    Bodrum
    Posts
    683
    Rep Power
    399
    I show the dates to the customers using a calendar in the form, M
    When they select the date, e.g. 01/10/2007, the calendar handles the right format.

    Using datepart function I am able to replace 01/10/2007 to 10/01/2007.

    after getting the format 10/01/2007, it stores as 01/10/2007.

    Am I doing something wrong M?
  6. #4
  7. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,652
    Rep Power
    2685
    y do u want to change the format while saving in db?
    it'll save in MM/DD/YYYY format only.

    U can change the format when u show it on page.
  8. #5
  9. Lost In Real World
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2006
    Location
    Bodrum
    Posts
    683
    Rep Power
    399
    M, I am able to change how it looks on the page.

    eg. rr_txtOrderDate is the date from user selection which is dd/mm/yyyy
    Lets say 25/10/2007

    Then I convert the using Datepart
    Code:
    intDate=DatePart("m", rr_txtOrderDate)& "/" & DatePart("d", rr_txtOrderDate)& "/" & DatePart("yyyy", rr_txtOrderDate)
    Then I get 10/25/2007. It is supposed to save into database as it is.(at least I have known like that!)

    But while inserting, it again converts the date into 25/10/2007.

    I am not sure whether it is safe to manipulate the field in that way or should it be another efficient way?

    What would you say M?
  10. #6
  11. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,652
    Rep Power
    2685
    DS, now u r confusing me!
    first u said that its inserting in MM/DD/YYYY format........ now u saying that its converting to 25/10/2007.

    May b its time that some expert jump in here
  12. #7
  13. Lost In Real World
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2006
    Location
    Bodrum
    Posts
    683
    Rep Power
    399
    It now works M right now but as I said I am not sure whether it is the right approach only?
  14. #8
  15. Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Aug 2005
    Location
    Moscow, Russia
    Posts
    327
    Rep Power
    21
    Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

    The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.
    (c) BOL, datetime and smalldatetime

    You may use
    SET DATEFORMAT DMY
    INSERT ...

Similar Threads

  1. US Date Format in IIS
    By buzyonok in forum ASP Development
    Replies: 4
    Last Post: January 10th, 2006, 01:45 AM
  2. Detecting Client Mail Format and Depending on it, either Hide of Display appropriate
    By SimplySatishD in forum HTML, JavaScript And CSS Help
    Replies: 1
    Last Post: July 20th, 2005, 03:20 AM
  3. Replies: 1
    Last Post: August 30th, 2004, 11:08 AM
  4. Displaying Dates in DD/MM/YYYY format
    By msaunders in forum Microsoft Access Help
    Replies: 3
    Last Post: May 27th, 2004, 07:08 AM
  5. Date format problems in Access2000, need your help!
    By mauron in forum Microsoft Access Help
    Replies: 1
    Last Post: December 9th, 2003, 01:34 PM

IMN logo majestic logo threadwatch logo seochat tools logo