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


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

Urgent Expression Request - Date Conversion

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
 

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 




View Complete Post


More Related Resource Links

URGENT HELP GUYS - Expression Request

  
Hi Guys,   I got a data coming through from derived column like this  192752_20052010 I want an expression to above column change to get only the last part to change like this 192752_20100520   Please help guys it urgent   Thanks,   D  

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


Expression for 24 hours time and date in SSRS

  
Hi Guys, What is the expression for  displaying 24 hours time and date in SSRS? Is this the format: =Format(now, "MM/dd/yyyy HH:mm:ss")?Thanks Shiven:)

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

Regular Expression for Date

  
Hi friends can you please tell me about a regular expression that can be used to validate all date formats depending on the culture info given Thanks in advance Nicsam

Expression Help -URGENT

  
Hi Guys, I am using foreeach loop and using data flow  task to insert data into table. I got two columns FileName and Filepart both data type are string. However I got a issue with data conversion.  Input Data = String Column Name = FileName Sample Data   FileName ProductCatalog_180915_20100518_201099.csv ProductCatalog_180915_20100520_201099.csv ProductCatalog_180915_04062010_201077_201082_04134.csv   I am using following expression for file part REPLACE(SUBSTRING(@[User::FileName],1, FINDSTRING( ( @[User::FileName] ), "_", 3) -1), ".CSV", "")   Output getting: ProductCatalog_180915_20100520 ------- Correct Format ProductCatalog_180915_04062010 ------- Wrong Format   I understand FileName from foreeach is getting that info, but before I insert that data into temptable. I want all my data in this format. ProductCatalog_180915_20100520   Can anyone shed some light how to over come this issue. Do I need to get data individually to each column (e.g. 04062010) only and change it or else can I use derived column. If I get individually how to identify which one is this (e.g. 04062010)  and this (e.g. 20100520)  thanks,   D 

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,        

Urgent Query Request

  
Hi Guys, Task of my package is to move FolderA csv files only to separate folder. However, the business rule is to move only those files which are matching in FolderB csv files. But I have to read the file first and get the distinct on Date Column and StoreId. Now, I build package using two foreeach loops one for folderA and one for folderB. Now in side the foreach loops I got dataflow where the files got read and using sort I removed all the duplicates and dump into temptableA and temptableB. My temptables column names are  DestinationFileName                                           Source PluSales_119809_20100421040237              \\fw-sql01\E\PROCESSED_FILES\PluSales_119809_20100421040237 PluSales_119809_20100421040237              \\fw-sql01\E\PROCESSED_FILES\PluSales_119809_20100421123456 Source column is actually a strFileName mapped in foreeach loop.   After loading the files, I am using execute sql task query for matching files and non matching files then using foreach loop and file system task I am moving files to separate folder.    Assuming, after completion of dataflow task,  each file dumped 2 distinct records with same storeID and different source name as

To Pass date in view at runtime!! Can anyone help Very Urgent

  
Hey I have to pass the Bussiness date in my query at run time I have tried the query below but not able to Execute it. Select * from table name where and RT.BUSINESS_DATE = ? --if only one date --if between two dates and RT.BUSINESS_DATE Between ? and ? Can Any one help me with the Syntax. Will be greatfull!!!

WCF HTTP POST - The remote server returned an error: (400) Bad Request. - URGENT

  
client & sever code below-------------------------------------------- [ServiceContract(Namespace = "http://test.mydomain.com")] public interface IMyService{[WebInvoke(UriTemplate = "", BodyStyle = WebMessageBodyStyle.Bare, Method = "POST")] Stream SaveXML(Stream input); } ----------------------[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)] [ServiceBehavior(Namespace = "http://test.mydomain.com")] public class MyService : IMyService{  public Stream SaveXML(Stream input)   {              StreamReader streamReader = new StreamReader(input);       string rawString = streamReader.ReadToEnd();       streamReader.Dispose();        // here need to save the input stream to xml format file          //       Encoding encoding = Encoding.GetEncoding("ISO-8859-1");       WebOperationContext.Current.OutgoingResponse.ContentType = "text/xml";         byte[] returnBytes = encoding.GetBytes(rawString);       return new MemoryStream(returnBytes);   } } --------------------------------------------------Client Code         string postData = GetXmlString("d:\\text.xml"); // user function        WebRequest request = WebRequest.Create("http://test.mydomain.com/myservice.svc");        request.Method = "POST";        byte[] byteArray = Encoding.UTF8.GetBytes(p

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


Data Expression YYYY-MM-DD - URGENT HELP GUYS

  

Input Data: 2010-03-23 00:00:00

Data Type: STRING

Expression Used: SUBSTRING(@[User::ExtractionDate],1,4) + "-" + SUBSTRING(@[User::ExtractionDate],6,2) + "-" + SUBSTRING(@[User::ExtractionDate],9,2) 

 

Current Output - 2010-03-23

 

However, my requirement is to get time as well 00:00:00, but only if they are 00:00:00 after the date in side the row.

OutPut Required only: 2010-03-23 00:00:00

Thanks,

 

 

 

 

 

 


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. 


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