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


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

Date conversion issue

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

Hi,

I am using 2 SQL instances, one is remote on the server and one local on my machine.  Both instances have British date format settings, but one performs different to another.  I am calling a function in SQL Server from Access VBA.

For example, when I pass a date to a local instance in this format #31/03/2011# it doesn't work, although the date settings is British.

But when I pass to remote instance works fine on both formats, #31/03/2011# and #03/31/2011#  also the date settings is British.

I dont' understand why this happening. 

Can I also ask how to pass a date from MS Access VBA to SQL Server 2008 R2 in this format: yyyymmdd ?  As I thought the format is universal within SQL Server and should interpret dates regardless on settings.

 

Thank you




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


Issue with date addition

  

Hi,

I am using ASP.NET, C# 2.0.

I am having a date issue. The error is "Year, Month and Day parameters describe an un-representable Datetime"

I am adding the date with 14 and 7 depending on a condition.

Below is the code I have used:

DateTime d = DateTime.Now;
int addDays =0;

if (Convert.ToString(Session["School"]) == "US" )
{  
addDays = 14;
}
else
{  
addDays = 7;
}

DateTime n = new DateTime(d.Year, d.Month, d.Day + addDays);



 

 The issue happens, if the date is 17 for US Schools and 25 for other schools, if the month has 30 days. This issue will happen for 28 days in February, and 30,31 days for other months.

How to add this 14 and 7 depending on the months. I know i have to increment the month by 1 and increase the date to the next month( and increase year to next, if done in december). But not sure, how to do this.

How to add this?

Thank you


Deserialization issue with returned from web service

  
Hi, I have a VS 2008 C#  client using a proxy generated by the Service Reference tool from a schema for a (prob Java?) ASMX web service.  I got serialisation errors when I tried connecting to the service around<xs:date> date fields, and on inspecting the incoming XML discovered the date format was yyyy-mm-dd hh:mm:ss.sss.   So 2 questions really.. 1) Is the schema I've been supplied with incorrect? 2)Is there a way to work around this, apart from manually editing the schema to xs:datetime and regenerating the classes?   Thanks MJ

Issue with data conversion

  
Hi Guys,   Input data format: 9261.00001e+012 Out put Required in NVARCHAR  I am using below expression in derived column but still its not converting. (DT_WSTR,50)new_master_plu Any help much appreciated. Thanks,            

Weird Date vs Date/Time issue using a calculated column

  
I'm attempting to use the fab 40 attendance template. I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!) I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]' When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?

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

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. 


Issue with CAML Query regarding date comparision.

  
I have a below query in one the eventhandlers. All I do in the query is to get all records where Next_Date column is < CurrentDateTime ( I have shown current date below in full date format but it will be variable).

What I am observing is the comparision seems to be failing or not considering time format. For example the below query should not return any records when NEXT_DATE is 9/30/2010 23:59:59 and CurrentDateTime is 9/30/2010 06:22:22. However the query does return records. It looks like the comparision is just happening with Date and not time. What could be the problem? Please suggest.

<Query>
   <Where>
      <Leq>
         <FieldRef Name='Next_x0020_Date' />
         <Value Type='DateTime'>2010-09-30T13:13:13Z</Value>
      </Leq>
   </Where>
   <OrderBy>
      <FieldRef Name='Next_x0020_Date' Ascending='False' />
   </OrderBy>
</Query>

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



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