Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Washington
    Posts
    4

    Question Unanswered: using bcp to output datetime in different format

    Hello:
    Using SQL2K, and BCP to (very quickly) port a tables data to a .csv file is great!
    However, the default format for the datetime values need to be YYYYMMDDhhmmss (with NO spaces).
    I've been spinning my wheels trying to come up with a way to do this.
    So far, this is what I have for the SELECT part - but, is there not an easier (or more elequent) way to accomplish this?
    (bcp is a single line command, so conversion must remain in a single cmd)

    -- format datetime to YYYYMMDDhhmmss with NO spaces
    SELECT u_name, u_type,
    str(year(u_datetime),4) + replace(str(month(u_datetime),2),' ','0') + replace(str(day(u_datetime),2),' ','0')
    -- don't know how to do the time part ??
    FROM t_user

    Any suggestions welcomed with highest regards..

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    convert your select into a view and bcp from it instead of the table itself.

  3. #3
    Join Date
    Sep 2003
    Location
    Washington
    Posts
    4
    Using a view will solve the aggregation problem (thanks), but I'm still unclear on how to properly display the TIME part of my format problem?
    (YYYMMDDhhmmss - with no spaces or : or /)

    Any ideas on that?

  4. #4
    Join Date
    Sep 2003
    Location
    Washington
    Posts
    4
    Using a view will solve the aggregation problem (thanks), but I'm still unclear on how to properly display the TIME part of my format problem?
    (YYYMMDDhhmmss - with no spaces or : or /)

    Any ideas on that?

  5. #5
    Join Date
    Sep 2003
    Location
    Washington
    Posts
    4
    Thank you for the view suggestion - that helped me take a different approach!
    Here is my resolve for displaying the datetime format as YYYYMMDDhhmmss without any delimiters;

    replace(replace(replace(convert(char(20), @now, 120),' ',''),'-',''),':','')

    If anyone has a better suggestion - please share!

Posting Permissions

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