Results 1 to 14 of 14

Thread: Convert

  1. #1
    Join Date
    Dec 2003
    Location
    Scotland
    Posts
    6

    Wink Unanswered: Convert

    I want to do this

    SELECT TOP 100 PERCENT CONVERT(smalldatetime, DOCDATE, 105) AS DOCDATE
    FROM SQSDBA.D_DETAILS
    WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
    ORDER BY ROWNO

    It is returning a date as 13/12/2003 ???

    Any Ideas ?

    Michael

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    what result are u expecting?

  3. #3
    Join Date
    Dec 2003
    Location
    Scotland
    Posts
    6

    Wink

    I expect 13-12-2003

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))FROM SQSDBA.D_DETAILS
    WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
    ORDER BY ROWNO

  6. #6
    Join Date
    Dec 2003
    Location
    Scotland
    Posts
    6
    Originally posted by harshal_in
    select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))
    woops i get 13-12-347 ??

  7. #7
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    oh sorry!!
    select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(yy,docdate))FROM SQSDBA.D_DETAILS
    WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
    ORDER BY ROWNO

  8. #8
    Join Date
    Dec 2003
    Location
    Scotland
    Posts
    6
    Originally posted by harshal_in
    oh sorry!!
    select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(yy,docdate))FROM SQSDBA.D_DETAILS
    WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
    ORDER BY ROWNO
    Thanks very much works a treat

    Very much appreciate your Help

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Grievem,

    Your query is (was) actually returning a datetime datatype which was being displayed in a different format than you desired.

    Ideally, you should leave it be, and then let whatever interface you use do the formatting you want. This is because if you convert it to a character field you will have difficulty sorting or grouping on the results.

    Let SQL Server do the calculations and the data retrieval, which it is best at, and let the interface do the formatting, because that is what it is best at.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Dec 2003
    Posts
    454
    blindman's points are good. In your query, it is not neccesary to convert one data type to another. You can do it on your program, i.e., ASP code.

  11. #11
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    I do agree with both of u it is not necessary to convert the datatype into varchar as it would affect the sorting and other stuff.The best way to format it is in the front end.
    regards,
    harshal.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by blindman
    Ideally, you should leave it be, and then let whatever interface you use do the formatting you want. This is because if you convert it to a character field you will have difficulty sorting or grouping on the results.
    this is quite true, insofar as it is good general advice, however, i would like to point out that ISO standard date formats do sort and group perfectly well

    furthermore, if you just "let it be", it may come out in a format that you will then have to unstring in your application code to get into the format you prefer!!! this makes the application code dependent on whichever default date format this particular installation uses, and blows you right out of the water if somebody changes the default!!!

    my advice: always display dates out of the database in ISO format

    also, a comment on this expression:

    CONVERT(smalldatetime, DOCDATE, 105)

    please note, the style value of the CONVERT function, e.g. 105 in the above example, is used to determine the display format for converting a datetime value to character string, not for helping the database to "unstring" or "interpret" or "decipher" a string value

    as far as i know, the database will convert a string into a datetime value depending only on whether it can recognize a valid date value in the string, and not by any helpful "hint" you may be trying to throw its way

    at least, that has been my experience, and that is also how i interpret the CAST and CONVERT documentation:
    style

    Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "please note, the style value of the CONVERT function, e.g. 105 in the above example, is used to determine the display format for converting a datetime value to character string, not for helping the database to "unstring" or "interpret" or "decipher" a string value"

    That's what I used to think as well, but I believe the point was made and demonstrated on this forum a few months back that the convert function will use the style value when deciding whether to interpret "01/02/03" as "Jan 2, 2003", "Feb 1, 2003", or "Feb 3, 2001".

    select convert(datetime, '01/02/03', 1)
    select convert(datetime, '01/02/03', 3)
    select convert(datetime, '01/02/03', 11)

    Results:
    2003-01-02 00:00:00.000
    2003-02-01 00:00:00.000
    2001-02-03 00:00:00.000
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoa

    shock and awe

    microsoft product actually works better than its documentation

    who'd'a thunk it


    thanks, blindman
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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