Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Question about formatting a date

    I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date
    My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You already did.

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
    Thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Buenos Aires, Argentina
    Posts
    20
    Something like this:

    select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date
    ------------------------------------
    Thanx
    ------------------------------------
    http://www.iespana.es/greatavatar/FIRMA/firma.jpg

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select convert(char(10), convert(datetime, '20040119'), 101)

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    Avatar,
    I don't suppose you could explain real quick how that works?
    Thanks.

  8. #8
    Join Date
    Oct 2003
    Location
    Buenos Aires, Argentina
    Posts
    20
    Im glad its work for you..

    Here is my explanation:

    1) CAST(my_date AS varchar(8)):
    Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
    2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
    Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
    3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
    Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
    4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
    Finally the RIGHT functions takes the first 10 characters of the string.

    I hope I explain well.
    ------------------------------------
    Thanx
    ------------------------------------
    http://www.iespana.es/greatavatar/FIRMA/firma.jpg

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
    Thanks again for your help and patience!!!

  10. #10
    Join Date
    Oct 2003
    Location
    Buenos Aires, Argentina
    Posts
    20
    Originally posted by exdter
    I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
    Thanks again for your help and patience!!!
    Oppss..!
    Now I'm looking better.. the RIGHT function is not necessary.
    The finaly version of the QUERY is like this:

    SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))

    Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.

    Ahh.. by the way:
    The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P

    What a mess..
    ------------------------------------
    Thanx
    ------------------------------------
    http://www.iespana.es/greatavatar/FIRMA/firma.jpg

  11. #11
    Join Date
    Aug 2003
    Posts
    328
    Thanks a whole lot!!!

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...In other words, this should have worked to begin with:

    select convert(char(10), convert(datetime, '20040119'), 101)

    The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:

    select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)

    101 - MM/DD/YYYY
    103 - DD/MM/YYYY

    Take your pick!

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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