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


Post New Web Links

Using LEFT command with Date Conversion

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
 
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


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

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

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,        

Hiding Command Promp window during ffmpeg conversion of video files

  
hi Can any body tell me how to  Hide Command Promp window during ffmpeg conversion of video files which comes up when we select a file to be converted.I want to hide the window because it comes in between the running project i want it to execute the files without showing that window to user regardsHArry 

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



Date Conversion in Function

  

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

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!


date type conversion

  

how to convert 2010-10-10 to numeric or int datatype ?

thanks .


Date Time conversion error

  

Hello all

I am trying to create a stored procedure that filters data from sql server by date but i get this error:

Conversion failed when converting datetime from character string.

my stored procedure is like:

CREATE PROCEDURE [dbo].[frRecordsUniqueBProductAndDatesSortedBDate]
(
	@productId int, 
	@dateFrom DateTime,
	@dateTo DateTime
)
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(4000)
DECLARE @productIdFilter nvarchar(10)
DECLARE @dateFromFilter DateTime
DECLARE @dateToFilter DateTime

SET @productIdFilter = Convert(nvarchar(10), @productId)
SET @dateFromFilter = Convert(DateTime, @dateFrom)
SET @dateToFilter = Convert(DateTime, @dateTo)

Set @sql = 
'select * from tableName where productId = ' + @productIdFilter +
' and myDate >= ' + @dateFromFilter + 
' and myDate <= ' + @dateToFilter


the parameters are passed to the stored procedures as dateTime variables from c# code...

any suggestions?


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