Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors

DataBinding: System.Data.DataRowView does not contain a property with the name Component_P

Hi,

Seems like a lot of people are having a similiar problem that I am having right now, but I am not able to find the solution to it. On the Page_load event, the gridview does display the data from database. When I click a button to insert the same data but different Waste_Profile_Num value, it gives me the databinding error. Component_Profile_ID is declared as an Identity and it is the primary key to the table. Anyway help?

Ok, the following are code that I have:

1 <asp:SqlDataSource ID="sqlDSMaterialComposition" runat="server" ConnectionString="<%$ ConnectionStrings:HAZConnectionString %>"2 SelectCommand="SELECT dbo.Component_Profile.Component_Profile_ID AS Component_Profile_ID, dbo.Component.Component, dbo.Component_Profile.Concentration, dbo.Component_Profile.Range, dbo.Component_Profile.Waste_Profile_Num, dbo.Component.Component_ID FROM dbo.Component INNER JOIN dbo.Component_Profile ON dbo.Component.Component_ID = dbo.Component_Profile.Component_ID WHERE (dbo.Component_Profile.Waste_Profile_Num = @.Waste_Profile_Num)">3 <SelectParameters>4 <asp:SessionParameter Name="Waste_Profile_Num" SessionField="Waste_Profile_Num" Type="Int32" />5 </SelectParameters>6 </asp:SqlDataSource>789 <asp:GridView ID="GridViewMaterialComposition"10 runat="server"11 DataKeyNames="Component_Profile_ID,Component_ID"12 AutoGenerateColumns="False" ShowFooter="true">13 <Columns>14 <asp:BoundField DataField="Component" HeaderText="Component" SortExpression="Component" FooterText="Total"/>15 <asp:TemplateField HeaderText="Concentration" FooterStyle-Font-Bold="true">16 <ItemTemplate>17 <%# SumConcentration(decimal.Parse(Eval("Concentration").ToString())).tostring("N2") %>18 </ItemTemplate>19 <FooterTemplate>20 <%# GetConcentration().tostring("N2") %>21 </FooterTemplate>22 </asp:TemplateField>23 <asp:BoundField DataField="Range" HeaderText="Range" SortExpression="Range" />24 <asp:BoundField DataField="Component_Profile_ID" HeaderText="Component_Profile_ID" ReadOnly="True" SortExpression="Component_Profile_ID" Visible="true" />25 <asp:BoundField DataField="Component_ID" HeaderText="Component_ID" ReadOnly="True" SortExpression="Component_ID" Visible="true"/>26 <asp:BoundField DataField="Waste_Profile_Num" HeaderText="Waste_Profile_Num" ReadOnly="true" SortExpression="Waste_Profile_Num" Visible="true" />27 <asp:CommandField ShowEditButton="true" />28 <asp:TemplateField Visible="false">29 <ItemTemplate>30 <asp:Label ID="lblComponentProfileID" runat="Server" Text='<% # Eval("Component_Profile_ID") %>'></asp:Label>31 <asp:Label ID="lblComponentID" runat="Server" Text='<% # Eval("Component_ID") %>'></asp:Label>32 </ItemTemplate>33 </asp:TemplateField>34 </Columns>35 </asp:GridView>

The following are the code-behind:

 
1 sqlSelect ="SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Component_Profile.Waste_Profile_Num = " & previousWasteProfileNum &")"2 sqlDSMaterialComposition.SelectCommand = sqlSelect3Try4 Dim dvCompositionAs Data.DataView =CType(sqlDSMaterialComposition.Select(DataSourceSelectArguments.Empty), Data.DataView)5For Each drAs Data.DataRowIn dvComposition.Table.Rows6 insertSql ="INSERT INTO Component_Profile ([Range], [Concentration], [Component_ID], [Waste_Profile_Num]) "7 insertSql &="VALUES (" & dr("Range").ToString.Trim &", " & dr("Concentration").ToString.Trim &", " & dr("Component_ID").ToString.Trim &", " &CInt(Session("Waste_Profile_Num").ToString()) &")"8 sqlDSMaterialComposition.InsertCommand = insertSql9 sqlDSMaterialComposition.Insert()10Next11 Catch exAs Exception1213End Try
  

Hi guys,

that's ok. I have figured that out. I just need to databind the gridview after the for each statement