Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Unanswered: simple date format question

    Hello Friends ,

    As Iam a very beginner to this sqlserver arena , Iam getting more and more doubts. Can anyone of you help to display a date as string type which is of format, "date/month/year hour:month:second:millisecond".

    Please get rid of the huge command which Iam using now,

    select rtrim(cast(datename(day ,getdate()) as char )) + '/'+ rtrim(cast(datename(month ,getdate()) as char )) + '/' + rtrim(cast(datename(year ,getdate()) as char )) + ' ' + rtrim(cast(datename(hour ,getdate()) as char )) + ':' + rtrim(cast(datename(minute ,getdate()) as char )) + ':' + rtrim(cast(datename(second ,getdate()) as char )) + ':'+rtrim(cast(datename(millisecond ,getdate()) as char )) as date

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look up CAST and CONVERT in Books Online.

    select CONVERT (varchar(50) , getdate(), 113) as date
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, for your specific format I think you will need to do it like this:

    select CONVERT (varchar(50) , getdate(), 103) + ' ' + CONVERT (varchar(50) , getdate(), 114)

    Result:

    06/04/2004 09:53:16:740
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Kuwaiti format is:
    Code:
    SELECT Convert(VARCHAR, GetDate(), 131)
    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Pat Phelan
    Kuwaiti format is:
    Code:
    SELECT Convert(VARCHAR, GetDate(), 131)
    -PatP
    SELECT Convert(VARCHAR, GetDate(), 131)

    ------------------------------
    16/02/1425 10:38:05:000PM

    (1 row(s) affected)


    I am confused
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by blindman
    Actually, for your specific format I think you will need to do it like this:

    select CONVERT (varchar(50) , getdate(), 103) + ' ' + CONVERT (varchar(50) , getdate(), 114)

    Result:

    06/04/2004 09:53:16:740
    Lindman does dates...

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Enigma
    SELECT Convert(VARCHAR, GetDate(), 131)

    ------------------------------
    16/02/1425 10:38:05:000PM

    (1 row(s) affected)


    I am confused
    What confuses you? I think that is what the original poster asked for (although by default Kuwati time uses a 12 hour clock instead of a 24). Did I miss something?

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    SELECT Convert(VARCHAR, GetDate(), 131)



    select getdate()


    ------------------------------
    16/02/1425 10:44:37:437PM

    (1 row(s) affected)


    ------------------------------------------------------
    2004-04-06 22:44:37.437

    (1 row(s) affected)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Enigma
    16/02/1425
    Oh yeah, I see the problem now! I forget that most people only think in one calendar.

    Kuwati time works on the Islamic calendar too, based in Mecca. Sorry!

    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Not documented in BOL ...

    And that is a date which is out of SQL server range according to BOL

    I am amused ... thats why I was confused
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are probably right though, based on the original posting they probably wanted the Gregorian date expressed in the Kuwati notation. That seems like a disaster waiting for its next victim to me, but if they want it then I guess that I'm Ok with it... My suggestion would be:
    Code:
    SELECT Convert(CHAR(11), GetDate(), 103) + Convert(CHAR(12), GetDate(), 14) AS date
    -PatP

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Enigma
    Not documented in BOL ...
    Sure it is, at least in my copies of BOL under CAST and Convert-PatP

  13. #13
    Join Date
    Nov 2003
    Posts
    11
    Thank u all for ur wonderful replies

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by chprvkmr
    Thank u all for ur wonderful replies
    I'm just being woefully nosey at this point, but what exactly did you originally want? More than anything, what I'm trying to figure out is how to answer the next person that poses a similar question.

    Thanks!
    -PatP

Posting Permissions

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