Hi I am copying data from excel to sql server 2005.
But when I copy it, In the time column when I have written for example 19:00 then in sql server it will write: 30-12-1899 19:00:00

And when I wan't to extract it to my page it will write it out as: 30-12-1899 19:00:00
but I only wan't it to be written like this: 19:00 ?

My sql statement looks like this:

"SELECT * FROM PROGRAM WHERE DATE >= convert(varchar, GETDATE()) AND DATE <= DATEADD(Month, 2, GETDATE())";

and my code that extracts it looks like this:

<asp:Repeater ID="program" runat="server">
    
    <ItemTemplate>
    <table >
        <tr>
        
        <td style="vertical-align:top" id="program_txt">
       <div id="txt_udsendelser"> <%# Eval("tid") %></td></div>
      <td style="vertical-align:top" class="program_txt">
      <%# Eval("txt")%></div> </td> <br />
      
        </tr>
      </table>

</ItemTemplate>
    </asp:Repeater>

you can see the result here:
http://radio.web.surftown.dk/udsendelser.aspx

Hope someone can help?

Kischi

You have a couple of options, and one I would recommend would be to change the database column type.

You can change the database column type from date/datetime to nvarchar or char format. This way you save "19:00" and not the whole date and time. Then when you retrieve it, you do not have to parse it, split it, and only take what you need.

Another method will consider always that you will be saving in that format, date / datetime. You can split the return string twice, once at a space, once at the colons. Then show the new array with positions one and two, and a colon between. An example of this would be below:

...
...
<%# ToTime(Eval("txt")) %>
...
...
public string ToTime(string time)
{
    //Create a new array.
    string[] str = new string[2];

    //split the given format in to two, separating the date and time by the space.
    str = time.Split(' ');

    //Reassign time to the actual time
    time = str[1];

    //Split the time into an array, and overwrite the previous array.
    str = time.Split(':');

    //The above three lines can be converted into one, below:
    //str = time.Split(' ')[1].Split(':');

    //Add the hours, a colon, and the minutes, and return it all.
    return str[0] + ":" + str[1];
}

Very simple, and will produce the output you expect. There are better ways as well, but this is easiest (in my opinion) to understand.

This article has been dead for over six months. Start a new discussion instead.