AM / PM is ignored when loading dates

Question

I use freebcp utility on unix to upload a flat file to a SQL Server 2008 table

I have the contents of a file as MAY 10 2013 01:00:01.000 PM , which is loaded into the sql server ( datetime ) table column.

when I select from this table ie select datecol from test I get the output 2013-05-10 01:00:29.000 but it should return 2013-05-10 13:00:29.000

even when I select convert(varchar,datecol,100) from test I get the output May 10 2013 12:00AM

How to import value correctly?

Answer 1

You can reuse this C # code on the Front-End side to convert the standard time format to military format.

 public String convertToMilitaryFormatFromStandardTime(String standardTime) { String[] split1 = time.Split(':'); String[] split2 = split1[1].Split(' '); if(split2[1] == "PM" || split2[1] == "AM") { split1[0] = (int.Parse(split1[0] + 12) + ""); } return split1[0] + ":" + split2[0]; }