Professional Web Applications Themes

BCP & BULK INSERT dates - Microsoft SQL / MS SQL Server

Mike, Please may we colonial Autralians use your date format too? Supposedly you can change your language settings and it all works. I am a little sceptical as I've just tried that, both on the OS level ( Regional Settings ), and adding a user whose language has the correct order dmy ( select * from master..syslanguages and pick one). The only format I can get it to accept is yyyy-mm-dd, which was my default setting before I started fiddling. I haven't rebooted, but I have stopped and started sql a few times. If you see a different format ( ...

  1. #1

    Default Re: BCP & BULK INSERT dates

    Mike,

    Please may we colonial Autralians use your date format too?

    Supposedly you can change your language settings and it all
    works. I am a little sceptical as I've just tried that, both on
    the OS level ( Regional Settings ), and adding a user whose
    language has the correct order dmy
    ( select * from master..syslanguages and pick one).

    The only format I can get it to accept is yyyy-mm-dd, which was
    my default setting before I started fiddling. I haven't rebooted,
    but I have stopped and started sql a few times. If you see a
    different format ( mdy ?) as the only accpeted one then there
    must be a way of changing it - I can't find it either though.

    An alternative that works is to use a staging table - importing
    the date columns as SQLCHAR, rather than SQLDATETIME
    in the format file, and doing the insert/convert in SQL server.

    HTH
    AJ

    p.s. It is a common complaint that the bcp doentation is lacking.


    "Mike" <com> wrote in message news:0bad01c360b7$bd7b1e30$gbl... 


    Andrew Guest

  2. #2

    Default Re: BCP & BULK INSERT dates

    Mike wrote: 

    Sorry, it may be more widely used, but logical?? Try sorting by it. :-) The
    most logical format is the ISO format: YYYYMMDD.

    Anyways, datetimes are not stored with any format. Read about the datetime
    datatype in BOL for details.

    Bob Barrows


    Bob Guest

  3. #3

    Default Re: BCP & BULK INSERT dates

    Agreed.

    I am familiar with the storage of dt types in sql, etc.

    However in a text file it is quite rare to represent it as
    a float or decimal. I just wish that MS would incorporate
    consistancy in translation, it would make life much easier.

    :S Ever hopeful
     [/ref]
    date 
    >
    >Sorry, it may be more widely used, but logical?? Try[/ref]
    sorting by it. :-) The 
    about the datetime 
    Mike Guest

Similar Threads

  1. BULK INSERT
    By sampsas23 in forum Coldfusion - Getting Started
    Replies: 10
    Last Post: January 12th, 01:50 PM
  2. Bulk Insert Error
    By Dan in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 10th, 06:52 AM
  3. Bulk Insert
    By Mirza in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 8th, 12:25 PM
  4. bulk insert command for vb?
    By Trint in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 5th, 03:42 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139