SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MSSQL Server + Date/Time Values

    Hi all,

    I used time and date types for my MySQL table fields.

    I need to migrate to MS SQL Server now so what types match the above?
    I tried timestamp but MSSQL won't allow multiple timestamps.

    Thanks.

    Kevin.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use DATETIME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    In MySQL I used date as 0000-00-00 format
    and time as 00:00:00 format.

    How do I ensure these will be the same in MSSQL Server?

    Thanks again.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    they won't, they'll both be DATETIME

    in SQL Server, DATETIME contains both date and time -- always

    are your separate date and time columns related to each other? then you simply combine them into a single value

    if they are not related, then you would save the date value into one DATETIME column, which would have times of midnight, and you would save the time values into a separate DATETIME column, which would have date values of january 1, 1900
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you would save the date value into one DATETIME column, which would have times of midnight, and you would save the time values into a separate DATETIME column, which would have date values of january 1, 1900
    Rudy,

    Do you mean date value to have date values and time value to have times of midnight???

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you insert '2006-01-18' (date only) into a sql server datetime column, you get 2006-01-18 00:00:00.000, and if you insert '9:37 AM' (time only) into a sql server datetime column, you get 1900-01-01 09:37:00.000

    so if you currently have separate date and time columns, and they are related, then combine them into a single datetime column in sql server

    if they are not related, then go ahead and use two datetime columns in sql server, but be aware that the are not separate date and time columns, they are separate datetime columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Apr 2004
    Location
    dublin
    Posts
    2,036
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Yes, they are related. But There will be a class date, a start time and an end time.

    So would I be better off to use 3 different datetimes? Or put start time with class date?

    Thanks for your help.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, only 2 columns: startdatetime and enddatetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •