problems converting dates as varchar to datetime data type

Hi all,

I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.

I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.

Someone can help me? PLEASE

Thanks

In what format do you have your dates at the moment?

You can try using SET DATEFORMAT dmy before running the statment.

Or look at using CONVERT with the relevant style.

eg CONVERT(DATETIME, YourDateField, 103)

Check Books Online for more details

HTH!

|||Try CONVERT function, make sure the style parameter matches your varchar date format
|||

If your date is in a recognizable format SQL Server uses an implicit conversion from varchar to datetime, so no CONVERT or CAST function is necessary. If the date is in an unrecognizable format you can use the T-SQL string functions (listed in BOL) to reconstruct it into a recognizable format and you should get no errors.

|||

Hi,

I will post some more information about my problem.

In the source DB i have the "Beginning_Date" column, as varchar, with values with this format "dd/mm/yyyy HH.MM.SS".

In the target DB i have the "Beginning_Date" column again, but must be only with the the date part (dd/mm/yyyy) as datetime type, and the "Beginning_Time" column, storing the time part (HH:MMS) as nchar type.

I have no problems with the time part of the records, but the date part always fail.

I use the substring function in order to get only the date part of the records and try to insert it to the target DB both with "dd/mm/yyyy" and "mm/dd/yyyy" date formats. I always get the same error (out of range). I have tried also the "set datetime dmy" .......

Thanks for your help,

Bob

|||
Are all your dates in Beginng_Date in the same format? Are you sure there isn't a rogue value in there which is causing the whole statement to crash?

Can you post your DDL and DML statements and some sample data for us to help troubleshoot further?

I had a guess at what you were trying to do:

Code Snippet

CREATE TABLE tblDateTime
(d1 DATETIME, t1 NCHAR(10))

DECLARE @.String NVARCHAR(30)
SET @.String = '21/12/2007 14:15:15'

SET DATEFORMAT dmy

INSERT INTO tblDateTime
SELECT SUBSTRING(@.String, 1, 10), REVERSE(SUBSTRING(REVERSE(@.String), 0, 9))

Is this anything like what you're doing?

|||

Hi,

The problem is solved now.

richbrownesq, you were right. My problem was only one (is enough...) record wrong.

Thanks a lot for ALL!!!!

Problem with UpdatePanel with Trigger, using MasterPage

Hi all,

I have a MasterPage with Calendar Control, and a content page with ListBox control.
My plan is to have a post back on the ListBox, when the Calendar control "SelectionChanged" event fires.
Both the Calendar control and the ListBox control are child elements of separate UpdatePanel controls.

I have this code fragment in the content page "Load" event handler.

if (!IsPostBack)
{
Microsoft.Web.UI.AsyncPostBackTrigger trigger = new
Microsoft.Web.UI.AsyncPostBackTrigger();
trigger.ControlID = master.CalendarStartDate.ClientID.ToString();
trigger.EventName = "SelectionChanged";
UpdatePanelItemMovers.Triggers.Add(trigger);
}

But when the page loads, I get this error:

A control with ID 'ctl00_ctl00_RightSidePlaceHolder_calendarStartDate' could not be found for the trigger in UpdatePanel 'UpdatePanelItemMovers'

Can someone tell me why?

Any luck on this. I'm having the exact same problem.|||I get the same problem using markup. This is the complete page, which uses a very simple master page that has just 2 content areas.

<%@. Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:ScriptManager id="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:Button ID="Button1" runat="server" Text="Click" /><br />
Time: <%= DateTime.Now.ToLongTimeString() %>
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
<asp:UpdatePanel id="UpdatePanel1" runat="server">
<contenttemplate>
Time: <%= DateTime.Now.ToLongTimeString() %>
</contenttemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Button1" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</asp:Content>

If I move the button into the Content2 placeholder, it works fine.|||One answer is to add the trigger using code:

protected void Page_Init()
{
Microsoft.Web.UI.AsyncPostBackTrigger trigger = new Microsoft.Web.UI.AsyncPostBackTrigger();
trigger.EventName = "Click";
trigger.ControlID = Button1.UniqueID.ToString();
UpdatePanel1.Triggers.Add(trigger);
}

see:
http://forums.asp.net/thread/1456042.aspx|||

I don't use Triggers any more, I just manually call UpdatePanel.Update() whenever I need to update a section of the page, and the result is exactly the same as using triggers. I believe the error can be traced to the MasterPage/Content relationship, where controls are assigned some strange-looking ID's.

|||Use the ChildrenAsTriggers="false" attribute in UpdatePanel for using AsyncPostBackTrigger.

Code Snippet

<asp:UpdatePanel ID="general" runat="server" UpdateMode="conditional" ChildrenAsTriggers="false">

<Triggers>
<asp:AsyncPostBackTrigger ControlID="bt_envia" EventName="Click" />
</Triggers>

Problem with UpdatePanel with Trigger, using MasterPage

Hi all,

I have a MasterPage with Calendar Control, and a content page with ListBox control.
My plan is to have a post back on the ListBox, when the Calendar control "SelectionChanged" event fires.
Both the Calendar control and the ListBox control are child elements of separate UpdatePanel controls.

I have this code fragment in the content page "Load" event handler.

if (!IsPostBack)
{
Microsoft.Web.UI.AsyncPostBackTrigger trigger = new
Microsoft.Web.UI.AsyncPostBackTrigger();
trigger.ControlID = master.CalendarStartDate.ClientID.ToString();
trigger.EventName = "SelectionChanged";
UpdatePanelItemMovers.Triggers.Add(trigger);
}

But when the page loads, I get this error:

A control with ID 'ctl00_ctl00_RightSidePlaceHolder_calendarStartDate' could not be found for the trigger in UpdatePanel 'UpdatePanelItemMovers'

Can someone tell me why?

Any luck on this. I'm having the exact same problem.|||I get the same problem using markup. This is the complete page, which uses a very simple master page that has just 2 content areas.

<%@. Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:ScriptManager id="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:Button ID="Button1" runat="server" Text="Click" /><br />
Time: <%= DateTime.Now.ToLongTimeString() %>
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
<asp:UpdatePanel id="UpdatePanel1" runat="server">
<contenttemplate>
Time: <%= DateTime.Now.ToLongTimeString() %>
</contenttemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Button1" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</asp:Content>

If I move the button into the Content2 placeholder, it works fine.
|||One answer is to add the trigger using code:

protected void Page_Init()
{
Microsoft.Web.UI.AsyncPostBackTrigger trigger = new Microsoft.Web.UI.AsyncPostBackTrigger();
trigger.EventName = "Click";
trigger.ControlID = Button1.UniqueID.ToString();
UpdatePanel1.Triggers.Add(trigger);
}

see:
http://forums.asp.net/thread/1456042.aspx

|||

I don't use Triggers any more, I just manually call UpdatePanel.Update() whenever I need to update a section of the page, and the result is exactly the same as using triggers. I believe the error can be traced to the MasterPage/Content relationship, where controls are assigned some strange-looking ID's.

|||Use the ChildrenAsTriggers="false" attribute in UpdatePanel for using AsyncPostBackTrigger.

Code Snippet

<asp:UpdatePanel ID="general" runat="server" UpdateMode="conditional" ChildrenAsTriggers="false">

<Triggers>
<asp:AsyncPostBackTrigger ControlID="bt_envia" EventName="Click" />
</Triggers>