.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Data Conversion - string to datetime help!!!

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi all, I need help on concatenate two fields into one then display it as datetime format as such yyyy-mm-dd hh:mm:ss AM/PM. The db is on SQL 2008 SP1. The date and time columns are on the same table and store as varchar. Date has varchar(10) and time has varchar(16). Date column shows result from query is 20100827 while the time column is either 070101.000000 or 070101. The question is how go about concatenate the two together and display in the format above in a report? How should I write my select query for this report? I am using SQL 2008 R2 BI Development Studio to design my report. Thank you. VCHS

View Complete Post

More Related Resource Links

Help to resolve an error "The conversion of a char data type to a datetime data type resulted in an

Why am I getting an error when executing this: select convert(datetime, '2010-09-12T18:11:48', 120) The message is: "Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I don’t get the error if I remove “T” from the string. But I need it to work with “T”.

Conversion failed when converting datetime from character string. SQL Server @StartDate as datetime



Can you please help me on this?

When I try to pass my parameter it doesn't work. See below is my code :


declare @SQL Varchar(4000)

declare @CRM_FilteredAppointment nvarchar(2000)

declare @CRM_FilteredSystemUser nvarchar(2000)

declare @StartDate datetime

declare @EndDate datetime

set @CRM_FilteredSystemUser = 'Select * from FilteredSystemUser'

set @StartDate = '20080101'

set @EndDate = '20100101'

set @CRM_FilteredAppointment = 'SELECT activityid, participationtypemask 

FROM FilteredAppointment 

WHERE FilteredAppointment.eu_calltype = 2

AND FilteredAppointment.statecode = 1

AND FilteredAppointment.scheduledend >= DateAdd( month, -1, GetD

DateTime/String Conversion


I have a few date fields on my report and i cannot get them to convert in the correct format.

The default is 08/28/2010 2:30:00 PM and I have used CONVERT(Varchar, EndDate, 103) to convert it to 28/08/2010. But the new date comes out as text format (string) when I copy results into Excel and I want it to stay as a Date type.

Can anyone help please?



string to datetime conversion using date parameters in SSRS


Hello, I have a database field that I am pulling that is type string, but the format of each value is mm/dd/yyyy.  I want to convert this to a datetime format (minus the time) so that i can compare this value to a parameter date range (begin date and end date) that are type datetime so that i can use the calendar control in my report.  i've tried in my select statement to use select.....convert(datetime, stringfieldname, 101), but I get an error that says "Conversion failed when converting datetime to character string". 

I am also open to converting the datetime parameters to strings to do the comparison, but not sure the best way to do this either.


extract picture data from a string


dears,advance thanks.can anybdy help to solve my problem which is describing below.

Example- %%D1,100,0,0004[data...][0d0a]

Head - %%D1
Amount of data package -100
The data package No. - 0
Data Length -1024 (0004 (low bit in front, high bit in back) 0004= 0x0400=1024;)

data- Original Picture data -1024(maximum)
Tail -0x0d0a
Data in hex

from the above example i want to extract picture data and need to store in to the sql table.sql table columname is image and datatype is also image.after that i wanted to display this image column to an aspx page.

expecting help asap.

WebMatrix conversion failed when converting from a character string to uniqueidentifier?


I am trying to retrieve xml from a small function in my .cshtml page. Its throwing me an error. This however, runs fine in a console/form environment.

It takes 3 parameters and 1 exception.

myDll.GetXML(year, username, uniqueidentifier, out ex);

When I run this page I get the error, "Conversion failed when converting from a character string to uniqueidentifier."

Or a stack of.

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBeha

Conversion from string to type 'Date' is not valid



Im receiving a strange error Error Message: Conversion from string "15/08/2010 22:21:35" to type 'Date' is not valid.

I know this is generally down to cultural date formats etc, however the reason this is strange is it just randomly occured twice in the last week, the website has been running since october and nothing has changed,

The error constantly occured until the IIS was reset and recompilled the site, its been running fine since, however im just trying to figure out what could cause such an error to occur.

I've narrowed it down to a line of code which is:

bktime = DReader.Item("Bktime").ToString

bktime = Year(CDate(bktime)) & "-" & fmt(Month(CDate(bktime)), 2) & "-" & fmt(Day(CDate(bktime)), 2)

Has anyone else encountered this randomly occuring and is there anything i can put in place to prevent this in future?



ASP.NET DateTime Conversion strange behaviour



I am working on ASP.NET web application where we are using javascript calendar to accept the date in textboxes. The date format is dd/mm/yyyy. The application is running in US and UK environments.

I am facing issues in US environment while storing the dates so I have added 2 keys in config file viz culture which will be either en-US or en-GB depending on the deployment location and baseCulture which will always be en-GB.

In the code, I have added a condition which checks if culture and baseCulture values are not matching then do the conversion of date in US format.

But I have found that the code converts the date from dd/mm/yyyy to mm/dd/yyyy when the key is baseCulture (en-GB) and it throws FormatException when key is culture (en-US).

I am bit confused by this behaviour. Is this normal? Can any one please explain more about this? Note: I am working on a machine which has en-US as language and culture

<add key="culture" value="en-US" />
<add key="baseCulture" value="en-GB" />
// This line works and converts the date from 26/08/2010 to 08/26/2010
// Here the baseCulture is en-GB.
DateTime dt = DateTime.Parse(txtCurrentDate,baseCulture)

// This line throws FormatException. culture 

Reading xml data from xml string




i have string like below:

<?xml version="1.0" encoding="utf-8" ?>










Data validation for datetime parameter in SSRS

Hi,   I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:   Public Function Validate( ByVal startdate As String) As BooleanIf IsDate(startdate) = True Then Return TrueElse Return FalseEnd IfEnd Function   And my report has a textbox which has the expression property set to; =Code.Validate(Parameters!startdate.Value) the textbox on the report has to display if the entered date is valid or not.   But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.   Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.   Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

Need help in datetime conversion excluding invalid records

I have varachar(50) field which have date in yyyymmdd format Now i have to filter the data which are greater then server date I have following query Select * from MDB_CONTENT_INFO Where convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate() Problem ist aht i may have some invalid data other then valid one. I am getting error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. or Conversion failed when converting date and/or time from character string. I wanted to exclude those records but currently getting error for the query asKamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

Sql DateTime2 to .NET DateTime conversion results in out-of-range exception

I posted it in C# General because I thought that the subject was not Sql Smo related, although I use Smo extensively even in this routine for connection. Unfortunately after a few days no constructive suggestion has been advanced. Two posts the thread collected are not helpful. I am wondering if Alok or someone else with the status of MSFT Sql Server developers take a look at this very importan problem for me which became a stumbling block.Thanks. AlexB - Win_7 Pro64, SqlSer64 WinSer64

Convertion failed when converting datetime to String

DECLARE @usr datetime SET @usr = user+'::Date' IF NOT EXISTS (SELECT 'X' FROM table WHERE Name = 'File' AND CONVERT(varchar,DateAdd, 101) = CONVERT(varchar, getdate(), 101)) BEGIN INSERT INTO table (Code, Name, Date) VALUES ('DD', 'File',@usr) END the date will be created by another process and its value will be loaded in the variable DATE. I'm calling that variable here. I;m getting the error Convertion failed when converting datetime to String. how to resolve this..??

Convertion failed when converting datetime to String

DECLARE @usr datetime SET @usr = user+'::Date' IF NOT EXISTS (SELECT 'X' FROM table WHERE Name = 'File' AND CONVERT(varchar,DateAdd, 101) = CONVERT(varchar, getdate(), 101)) BEGIN INSERT INTO table (Code, Name, Date) VALUES ('DD', 'File',@usr) END I;m getting the error Convertion failed when converting datetime to String. how to resolve this..??

String Data missing start from "+" When Export Data To Excel Using Gridview

Hi,I am exporting data from gridview to excel file.My problem is that for all the string which contain "+", the rest of data begining from it is missing in excel file.For example: PWE-WER+78, when exported it will become PWE-WER.Below is my code.                Response.ClearContent() Response.Buffer = True Response.AddHeader("Content-Disposition", "attachment;filename=" & "ExportedExcel.xls") Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" lobjStringWriter = New StringWriter lobjHtmlWriter = New HtmlTextWriter(lobjStringWriter) dgdResult.RenderControl(lobjHtmlWriter) Response.Write(lobjStringWriter.ToString()) Response.End() I  have search through online but no solution.Does anyone has idea on this?

Data conversion in SSIS

I am creating an SSIS package, My source DB is ORACLE and destination is SQL Server 2005 I am moving the contents of a Table AO in Oracle DB to  Table B in SQL Server2005 A specific Column in  "Renvenue " in AO table is of data type NUMBER  , it contains  decimal values like 1040.334, 19191.33, 454545.22 I have a corresponding column 'Rev" in Table B ( in SQLserver DB) of datatype Numeric(18,2)   I have created a SSIS package using Data source Reader, DataConversion , OLEDB Destination  in the dataflow task Everything is working fine, but I am getting incorrect data for example 1040.334, 19191.33, 454545.22   for these values I am getting 1040.00 , 19191.00 , 454545.00 the decimal values are not getting transferred to the destination table. I tried converting the datatype to Numeric {DT_NUMBERIC] 18 -2   and even declimal(DT_DECIMAL] scale 2 but still not getting the decimal values   Can some one tell me where am I going wrong...      
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend