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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Date Conversion in Function

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server
 

I have a date conversion in a function that is failing to convert.

this fails when trying to convert

This uses a function to convert the date to a string

 

REFERENCE.dbo.UDF_FORMAT_DATE(S.PGI_DATE, 'YYYYMMDD') AS INV_YYYYMMDD

Error -

Conversion failed when converting datetime from character string.

below is the code for the functin

DECLARE @pos AS INTEGER

DECLARE @char AS VARCHAR(1)

--

-- Replace Year

--

	SET @pos = CHARINDEX('yyyy', @format)

	WHILE @pos > 0

	BEGIN

		SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))

		--PRINT @format

		SET @pos = CHARINDEX('yyyy', @format)

	END

	

	SET @pos = CHARINDEX('yy', @format)

	WHILE @pos > 0

	BEGIN

		SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))

		--PRINT @format

		SET @pos = CHARINDEX('yy', @format)

	END

--

-- Replace Month

--

	SET @pos = CHARINDEX('mmmm', @format)

	WHILE @pos > 0

	BEGIN

		SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date))

		--PRINT @format

		SET @pos = CHARINDEX('mmmm', @format)

	END

	

	SET @pos = CHARINDEX('mmm', @format)

	WHILE @pos > 0

	BEGIN

		SET @format = STUFF(@format, @pos


View Complete Post


More Related Resource Links

Conversion from string to type 'Date' is not valid

  

Hi

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?


Cheers


Mike


Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

Using LEFT command with Date Conversion

  
Hi There, I have a select statement that I use to seperate a datetime field in to a date and time column. select (convert(varchar(30),DateTimeField,107)) as Date,(convert(varchar(30),DateTimeField,108)) as Time FROM mytable I want to be able to trim off the last "00's" from the time column, so I have tried to use the LEFT command - which I am unable to get to work. I would have expected it to be: select (convert(varchar(30),DateTimeField,107)) as Date,LEFT((convert(varchar(30),DateTimeField,108)),5) as Time FROM mytable But I can not get it to return a value, Can anyone see where I am going wrong? Thanks Matt

Conversion failed when converting date and/or time from character string. SQL 2008

  
The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've  datatype of "Created" & "Finished" column in below table VARCHAR(100) select * from dbo.mytable where CONVERT(datetime,Created) >= convert(datetime,'9/1/2010') and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010') In my table i've date stored in below varchar(100) formats. examples: '2010-09-01 11:14:54.000' --Created '2010-09-03 13:43:37.000' --Finished I tried to convert the date in following way but NO luck??? Could you please advise me??? convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09') Much appreciated your help!! thanks, pbrathod pbrathod

How to Date function convert (09/13/2010 10:18:24 to 13.Sep.10)

  
 How to Date function convert (09/13/2010 10:18:24  to     13.Sep.10)passing a simple query no template used input  09/13/2010 10:18:24output 13.Sep.10

Date Conversion from dd/mm/yyyy to yyyy-mm-dd

  
Hi guys,   I need a expression to convert date format from dd/mm/yyyy to yyyy-mm-dd by using SUBSTRING, FINDSTING  COLUMN NAME: ExtractionDate   I tried something like this to get year first and later on to add mm and DD, but its not working   SUBSTRING(@[User::ExtractionDate], FINDSTRING((@[User::ExtractionDate], "/", 2)),4)   Any help much appreciated   Thanks,        

date time Conversion

  
select [RowID]=ROW_NUMBER() over (order by b.userid),b.userid [Data Capturer's],
COUNT(distinct(g.boxnumber)) [Count] from box b join General_SAM g 
on (g.BoxNumber=b.boxnumber) where b.code=5000 and b.department=02 and b.origindate 
between '2010-07-28 00:00:00.000' and '2010-09-28 00:00:00.000' group by b.userid 

My computer is set to UK - dd-mm-yyyy. 

Is there a way that i can supply date using the above format i.e. dd-mm-yyyy


Urgent Expression Request - Date Conversion

  

Input Date : 4/08/2010 6:27:00 AM

DATA TYPE : STRING

Output Required: 2010/08/04 

 

Expression used : SUBSTRING(@[User::ExtractionDate],1,FINDSTRING(@[User::ExtractionDate],"/",1) - 1) + "-" + SUBSTRING(@[User::ExtractionDate],FINDSTRING(@[User::ExtractionDate],"/",1) + 1,2) + "-" + SUBSTRING(@[User::ExtractionDate],FINDSTRING(@[User::ExtractionDate],"/",2) + 1,4)

Current Date is getting: 4-08-2010

 

URGENT HELP GUYS I NEED THE OUTPUT IN THIS FORMAT

2010/08/04 


SQL error: Conversion failed when converting date and/or time from character string.

  
I hope someone can advise on the issue I'm having.

This SQL worked beautiful until I added in @startdt and @enddt and this section >> AND [Selldate] BETWEEN ' + @startdt + ' AND ' + @enddt + '

By adding in this condition, I now get the following error:

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

I have tried changed variables to type = date and various iterations of date formatting, casting with no success. The date in the table is clean as a whistle.

(The CONVERT function is being used in setup for a PIVOT SQL which needs  VARCHAR for column headers)

Thanks in advance for your help, 
JR

declare @itemID varchar(20)
declare @startdt varchar(12)
declare @enddt varchar(12)
set @itemID = ''
set

Conversion from string "09/28/10 9.00AM" to type 'Date' is not valid.

  

I am getting the below error. Please see my code below and let me know what is wrong.

 

dim sDatetime as string = startdate & " " &starttime & "AM"

dim dt as Date = CDate(sDatetime)

I am getting the Conversion from string "09/28/10 9.00AM" to type 'Date' is not valid error in this line. 


Date Conversion Removing PM and AM

  

Hi Guys,

Nitesh Rai helped me with below expression. Thanks to him

But sometimes data will come either / or -. This is driving me crazy.

 

Input Data

31/03/2009 6:07:00 PM

2009-03-31 7:07:00 PM

Output Required

2009-03-31 06:07:00

2009-03-31 07:07:00

Expression Using 

SUBSTRING( Column1 , 7, 4 )  + "-" + SUBSTRING( Column1 , 4, 2 )  + "-&qu

Date Conversion Help

  

Hi Guys,

My input data is STRING in this format and the column name is [Column 1]

Input Data

31/03/2009 6:07:00 PM

Output Data Required

2009-03-31 6:07:00 PM

 

I need a expression to convert it, any help much appreciated.

 

Thanks in Advance,

D



several date ranges in one report - first/last function?

  

Hello,

    I´m working on a report with SSRS 2008 which is meant to show a lot of figures aggregated for the whole calendar year. In all datasets I have the date as a parameter (date with hierarchy: year - month). So to get the sums for the whole calendar year I only have to select all the months of the wanted year and SSRS shows the total for that period. That works fine. The problem:

Some figures (for which separate datasets are used) are not meant to be aggregated, eg the number of open cases at the beginning of the period or the number of open cases at the end of the period.

I tried using the First/Last-functions on that figures but that doesn´t work because the first/last function doesn´t seem to refer to the date-parameter but to other dimensions (in my case: region because it shows the summed up values for the first region instead of the not-aggregated values for the first month).

How can I acchieve that my report shows the first/last value dependent on the choosen date-parameters while summing up the other values for the whole choosen date-parameters?

 

Your help would be much appreciated.

NuramonTheWanderer

 


ssis date conversion

  

Hi,

We are trying data conversion  using ssis  and facing the following issues: 

1. How to default values to table (sql server) through SSIS in the data transfer step. (I used to run an update after the data transform task, but here the table schema does not allow nulls at all during the intial data transfer). It looks to me may be there is an option somewhere that i need to check for the default values.

2. We want to transfer data to a identity column, knowing that it does not let me export data into that column i actually added a  SQL step before the data flow task saying "SET identity_insert off" on that table but still the package fails saying that cannot insert on identity column.

3.we also want generate a script that shows how the data transformation is done like which table and column is mapped to which destination table and column.

please advice.

Thanks, 


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.

thanks!


Whether Date & Math function supported in WCF Data service

  

Hi,

Whether Data(day,year,hour,minute,month,second) and Math functions(double round, decimal round, double floor, decimal floor, double ceiling, decimal ceiling) are supported in WCF data service using ODATA.


Thank You Benita Blas
Categories: 
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