convert datetime to date

having issues with the reporting excel imports. problem with the import is that gridview has populated with "10/10/2009 12:00:00 am" instead of just "10/10/2009" as the original 'excel' cell only has date data listed in the column.


tried using convert without luck. checked the msdn forums and copied the exmaples, but this did not work in my scenario.

"SELECT convert(char(10),[month],101) * FROM [sheet5$]"


'Month' being the column name. there are other columns in the table which do not need the convert logic applied.


Here is the error message.


System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'convert(char(10),[month],101) *'


hope i can get some assistance with this issue.

10/28/2008 10:49:37 AM 1945 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 39

 do the cast and convert functions even work with

10/29/2008 10:31:05 AM


 do the cast and convert functions even work with



Guess I was kinda on the right track when I asked if  they were supported. They are obviously supported within SQL Server and so forth.

Importing from an excel file will utilise the MS Access engine driver on performing the query. So in actual fact the convert function need to be replaced with the format function.

Used the following with success.


SELECT Format([Month],'mmmm') AS [Month] FROM [sheet5$]


Wish it was only easier to determine the problem quicker, so many views and nobody was able to save me all that searching.. But the upside is now the understanding.


Thanks anyways..

10/30/2008 3:50:54 AM

Similar Artilces:

Issue when converting XML dateTime to .NET DateTime
Hi, I am experiencing an issue when deserializing XML dateTime nodes in ASP.NET in some timezones. The construct 0001-01-01T00:00:00.0000000+ZZ:ZZ where ZZ:ZZ is the timezone offset for the timezone on the current system should produce a DateTime value equalling the constant DateTime.MinValue when converted. This works some of the time, such as for the Paris time offset of +1: (CDate("0001-01-01T00:00:00.0000000+01:00") = DateTime.MinValue) = True But does not work for Sydney time offset of +10: (CDate("0001-01-01T00:00:00.0000000+10:00") = DateTime....

How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToSt...

Convert .NET DateTime format to SQL DateTime format
Hi!!I'd like to know if there is a function to convert the DateTime format used in C# (VS2005) : dd/mm/yyyy HH:mm:ss to the format used in SQL Databases: yyyymmdd HH:mm:ssThanks folks!...

How to convert Julian date into Calendar date (VB.Net)
Hi all, I have some dates which are in Julian format and I need to convert them into calendar dates  for example if the Julian date is 1, I need to be able to convert it into 1/1/2007, If the julian date is 66 then I would need to convert it to 3/7/2007 and so on. Every year would start with 1 and end with 365365 = 12/31/2007. Is there a function or tutorial somewhere ? thanks for any help.    1) Your dates aren't in Julian format!! 2) How do you know which year you are referring to? 3) Have you tried the AddDays function of the Date/DateTim...

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

Parse DateTime into Date or select DateTime as Date from Access
I am getting DateTime values out of an Access Database and I want to only use the Date part of this field. I have tried to cast the returned value to a DateTime value and then format the DateTime to only show the Date part in the toString() fcn. <asp:Label runat="server" Text='<%# getDate(DataBinder.Eval(Container, "DataItem.ACTIVE_FROM"))%>'> But I can't cast the object to a DateTime object. I have also tried using DateTime.Parse() but this gives an error saying that the format is not correct.         {       ...

convert date christian date to persian date
hi, I need to convert christain date that was produced in my Sql database to persian date like" دوشنبه 21 مرداد 1378" in a datalist.Could you recommend me any function.I use VisualStudio 2005 & VB. thanks in advance Sepid Take a read of or maybe a javascript route like Hope it helpsRegardsJeremyIf this has helped Please: Don't forget to click "Mark as Answer" on the post that helped you.That way future readers will ...

Converting Date from varchar to datetime
Hello, I need to convert a character string into a date, and then compare it with getdate(). What I am finding with convert, is I cannot get the time portion out. I seem to get the date and time portion together. As well, with the style parameter of 108 (which is supposed to return only hh:mm:ss) it is returning the full date with the time, as well as the AM/PM indicator. I need to make the comparision with the military date (i.e. no AM or PM indicators). Here is what I have done declare @date_hold varchar(12) declare @new_date datetime select @date_hold='18:12:12...

converting to from offered me some sample code when I signed up with them the only problem is the sample code is in but my page that they type all of their credit card into is <code><%@ Import Namespace="System.Net" %> <%@ Import Namespace="System.IO" %> <script language="C#" runat="server"> void Page_Load(Object Src, EventArgs E) { myPage.Text = readHtmlPage(""); } private String readHtmlPage(string url) { ...

converting string date to datetime format
Hi, I have string which consist of date value in "dd/mm/yyyy" format.  I need to convert it into datetime value.  How I will do this.  string sDate = "25/10/2007"; DateTime.Parse(sDate); It throws exception.  How I will solve this issue.  Regards, maxiMaxi DateTime.Parse Or DateTime.ParseExact comes with another parameter called IFormatProvider with that you can specify your culture language. Before you do parse, you need to do that first. System.IFormatProvider format =   new System.Globalization.CultureInfo ( "Your Lang...

convert datetime to just date format
I have a field in a table that I declard as datetime, however when the date populates in my form I only want the Date to show, not the time.   I believe i have to use a Select Convert(Varchar(10), DateOfVideo, 101   statement, but not sure if this would go in the code behind page or in SQL???? There is no "date only" type in .net. You need to format the date in the control you are using to display the date. Or, convert the date to a string in sql and treat it like a string in the code.Steve Wellens My blog in server side before populating  Dat...

convert to/from .NET's System.DateTime?
Is there a module/routine to do this? From .NET Framework SDK documentation: "Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 1 C.E. in the GregorianCalendar calendar. For example, a ticks value of 31241376000000000L represents the date, Friday, January 01, 0100 12:00:00 midnight." Of all the 64bit timestamp I've seen (others include TAI, NTP, PostgreSQL, Firebird, Win32 FILETIME), I've kind of like this one because the range and precision usually "feels right&qu...

Dates, Dates, Dates!
Hello all... I am in the process of building an appointment system, and I am trying to see if I can get some pointers on which way to go on this. The first version of this appointment system was very lo-tech, and it stored all of the possible dates/times in the database. Once a user booked an appointment, their information was logged to that date/time, and then that date/time would no longer show in the list of possible openings. This was great for a temporary fix, but with having to pre-populate all of the dates/times, the system would eventually have to be updated to continue on pa...

Convert Date/Time to Date
Hi I'm using .NET 2.0 framework with a Postgres DB. I'm getting date values from the table 'creationdate' and I need to compare it with other date value (given by me). The thing is that tha date returned from the DB comes like this '27-03-2006 0:00:00' and the date value that I use to campare comes like this '27-03-2006'. I want to know how to convert the returned date value from the DB to date only...?! I tried using the following: SELECT CONVERT(char(10),creationdate,101) AS date FROM WHERE date = "myDate"; but it gives me an error: syntax error at or near "," at c...

Web resources about - convert datetime to date -

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...

Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ... - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows ...

Resources last updated: 1/17/2016 1:14:41 PM