Converting date from an external source

I am having an issued with converting a date from external data source. the 
data has the timestamp in the general date form mm/dd/yyyy 00:00:00. I want 
to convert the date to mm/dd/yyyy format so when i run a query for a single 
day it will return the data for that date, I can currently return the data 
but i have to set the parameter in the mm/dd/yyyy 00:00:00 format, i want to 
simply return the data by setting the parameter in the mm/dd/yyyy format
0
Utf
6/4/2007 8:27:00 PM
access 16762 articles. 3 followers. Follow

1 Replies
800 Views

Similar Articles

[PageSpeed] 42

Don't confuse how data is stored with how it is presented.  As long as you 
import the date into a field defined as a Date data type, you can display it 
in any format you choose.  Be careful to not format a date too early in a 
process because formatting a date will convert it to a string and a string 
will not sort intelligently the way a data will.

To extract only the date part of a date/time field use the DateValue() 
function.  To extract only the time portion use the TimeValue() function.

Select ...
From ..
Where DateValue(YourDate) between Date() and Date()-7;  --- to return 
records within the past 7 days


"Chris Hood" <ChrisHood@discussions.microsoft.com> wrote in message 
news:8C46B8D9-67F5-46A9-A4AB-26240C384803@microsoft.com...
>I am having an issued with converting a date from external data source. the
> data has the timestamp in the general date form mm/dd/yyyy 00:00:00. I 
> want
> to convert the date to mm/dd/yyyy format so when i run a query for a 
> single
> day it will return the data for that date, I can currently return the data
> but i have to set the parameter in the mm/dd/yyyy 00:00:00 format, i want 
> to
> simply return the data by setting the parameter in the mm/dd/yyyy format 


0
Pat
6/4/2007 8:42:32 PM
Reply:

Similar Artilces:

Sorting by Date and Time
I transformed some data to an excel file. I tried to sort those data by date and time, however it didn’t work. I also tried to change the format of the date to like “mar 2 98” or “03/02/98”, but the appearance is always “3/2/98”. It just refuse to change the format. It would be appreciated if anybody could help with this. Hi when you right mouse click on one of the cells, go into format / cells & choose the number tab, click on general ... what does it say under the word sample on the right hand side? Cheers JulieD "plumstone" <plumstone@discussions.microsoft.com> wrote...

DATE Formulas #2
Hello, I wrote last time: I want to know what I will get paid TODAY, THIS WEEK or by the MONTH. I don't know how to get the Day (C2),Week (D2) and Month.(E2). Thanks for those who helped. Now: I must add that if today is Thursday I need to know what I will get paid Thursday - Saturday for the week and from the 29th - 30th of the month. In other words for the week and Month calculations it must be going forward from the current day not for the entire week or month if that time has past. If the weekday is Tuesday then I need from Tuesday to Saturday. What formula can I use to accomplish this...

Date and Time setup
I work at a 24 hour job we take readings every hour. I want to have the date autofill a cell as the current date but our sheet changes at 0700 every day. So if the sheet is worked on at 0100 the date needs to be for the past day starting at 0700, if the time for the current day changes to 0800 the cell will display the new date. ie: 0100hrs = 4/18/06 0600hrs = 4/18/06 0800hrs = 4/19/06 etc... =TODAY()-(A1<=TIME(7,0,0))*1 HTH -- AP "Joe" <jdbarnes63@earthlink.net> a �crit dans le message de news:28db731403ef4144bf9b3800591a428e@ureader.com... > I work at a 24 ho...

filtering dates within this week
I have a series of dates. How can i filter dates that are within this week? thanks I would add another column to represent the year and week number. Then filter on that. Excel has its own =weeknum() function (requires installation of the analysis toolpak in xl2003 and below). But week numbers mean different things to different people/groups/organizations. I'd start with Ron de Bruin's site if excel's =weeknum() isn't sufficient: http://www.rondebruin.nl/weeknumber.htm http://www.rondebruin.nl/isodate.htm Lynn wrote: > > I have a series of dates. How can i filter d...

How do I convert a document into the wallpaper on my screen?
I have created a document and want to use it for my wallpaper on my PC. Can anyone tell me the steps? On Dec 3, 9:14=A0am, Red14 <Re...@discussions.microsoft.com> wrote: > I have created a document and want to use it for my wallpaper on my PC. C= an > anyone tell me the steps? This has nothing to do with Word -- you do it in Windows -- but Windows seems to require a graphics format and can't use a .doc(x) for that. ...

How do I convert an excel spreadsheet into a pdf?
You need a pdf creator program to do that, there's a free one called PDFCreator at sourceforge.net Or you can buy one from Adobe.com Try this site http://www.rcis.co.za/dale/info/pdfguide.htm -- XL2002 Regards William willwest22@yahoo.com "pphelp" <pphelp@discussions.microsoft.com> wrote in message news:7DB46284-1138-441A-8E4B-3DF829C27EBE@microsoft.com... | I also recommend the method used on this site. Will make one PDF file from multiple sheets. Gord Dibben Excel MVP On Fri, 7 Jan 2005 23:45:29 -0000, "William" <willwest22@yahoo.com> wrote:...

Converting Publisher Files to PDF Format
When I try to convert Publisher files to PDF format I get a message that says "The size of the paper you have chosen is too small to show the printer markings", but when my commercial printing guy takes my same Publisher files and converts them, it works. What am I doing wrong. I've already talked to Adobe and they think that my Acrobat Distiller is set up properly. I have Adobe Acrobat 6.0 Standard, Windows XP Professional, Publisher 2003 and an IBM desktop with 260 gigabytes of memory ediejensen wrote: > When I try to convert Publisher files to PDF format I get a mess...

how to convert string to double datatype using VB.net
i want a convert a string to double datatype EggHeadCafe - Software Developer Portal of Choice JavaScript Bar Graph Control http://www.eggheadcafe.com/tutorials/aspnet/0995163b-451d-45fc-9a8f-57bba826f2a3/javascript-bar-graph-cont.aspx kadar ali schrieb: > i want a convert a string to double datatype Good question - because it shows that you switch Option Strict On (as recommended). http://msdn.microsoft.com/en-us/library/t8s7t9bf.aspx http://msdn.microsoft.com/en-us/library/hcb26cc8.aspx -- Armin "kadar ali" wrote: > i want a convert a string to...

Date Prompts in Subreport
Is it possible to combine 2 reports together if they are both based on queries that require date prompts? The reason I am asking is that I have 2 reports of employee time, each for a different department. Both are based on queries that have the criteria Between [Beginning Date:] and [Ending Date:]. That way, when each report is generated, the user can select the date range to display. Now, I am trying to combine them so that they both show up on the same report. I tried making one a subreport of another, but when I try to run the report, I keep getting the date prompts over and ove...

Changing how Excel INTERPRETS dates
Anybody know how to change the way Excel interprets dates? For the lif of me I can't remember. I don't just mean reformatting a cell. I mean if one would typ "8/11/04" Excel would read this as November 08, 2004 and not August 11 2004. Any hope would be much appreciated, Dav -- dgreenfiel ----------------------------------------------------------------------- dgreenfield's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1629 View this thread: http://www.excelforum.com/showthread.php?threadid=27689 Hi this is defined in your Windows - Region...

External Hard Drive
My external hard drive shows in device manager and computer management, but it does not show in my computer. Therefore I can't back up to the external drive. I have WIN Vista 64 bit. How can I correct this? Thanks you. On Nov 21, 9:44=A0am, ichn13 <ich...@discussions.microsoft.com> wrote: > My external hard drive shows in device manager and computer management, b= ut > it does not show in my computer. Therefore I can't back up to the externa= l > drive. I have WIN Vista 64 bit. > How can I correct this? > Thanks you. Go to "Disk Management&qu...

update /delete external refs
I have written a macro and want to se it on older workbooks.This requires that a new page replaces an existing page.However all the references come over with the new page.I want to use edit/find replace all to replace [JUL02]Frame!C$6 with Frame!C$6 ie delete all references to original worksheet.(or some other way!) I have figured it out!.I copy the new page over to the old worksheet and then change source of links back to itself! >-----Original Message----- >I have written a macro and want to se it on older >workbooks.This requires that a new page replaces an >existing pa...

How to convert 3GP to MP3
How to convert 3GP to MP3 http://www.softe.net/audio_converter/help/convert-3gp-mp3.php 3GP is a file format which is used in mobile phones to store media (audio/ video). This file format is a simpler version of "ISO 14496-1 Media Format". Audio is stored in AMR-NB or AAC-LC formats. When you recorded videos in 3GP format on your 3G mobile phone, you probably want them to be converted to popular MP3 files. Follow the steps below to convert 3GP files to MP3. Converting 3GP to MP3 using Eufony 3GP MP3 Converter http://www.softe.net/audio_converter/ 1. After launch E...

Converting access report to pdf format
hi I wrote a database that, among other things, prints a variety of reports for my clients. Once each report is done, I run a program called pdfWriter to convert each report to a pdf file. (When this program is installed, I select the printer called pdfWriter then print the file. It doesn't actually print a physical copy but saves the file in pdf format.) Then I e-mail these pdf reports to the clients. What I'm looking for are ways that I could automate this process from Access. Can this be done? If so, could someone point me in the right direction. Any clues would be grea...

How do I change the date taken on a picture using MS picture manag
I have pictures taken with a canon power shot a520. I want to change the date picture was taken. How do I do that? Podo373 <Podo373@discussions.microsoft.com> was very recently heard to utter: > I have pictures taken with a canon power shot a520. I want to change > the date picture was taken. How do I do that? This newsgroup is for the discussion of and asking questions related to the Microsoft Publisher product. What does this question have to do with Microsoft Publisher? See here for a tool to edit EXIF data: http://www.colorpilot.com/exif.html -- Ed Bennett - MVP Micr...

Cannot schedule an appointment for any day but the current date???
Hi. I'm running Outlook 2000 on Win2K and recently reformatted and performed a clean install. When clicking on a specific date to schedule an appointment in Calendar, the current date always appears in the date fields. After entering the proper date & information and saving, the appointment appears from the date I selected back to the current date. I am clueless, and wonder if I am overlooking a simple checkbox option. I ran the "Detect and Repair" utility and then reinstalled Office 2000 over the existing install, both to no avail. Thanks for your feedback. John New Orl...

Date Selection for Pivot Table
I build some pivot tables for our laboratuary. They contains daily analysis results. I want a solution to: 1-) User can enter week number and then daily analysis for that week appears day by day on a pivot table. Example: Input: 49 Output:29/11/2004---> 05/12/2004 on pivot table and regarding data 2-) User can enter month and than daily analysis for that month appears day by day on a pivot table. Example: Input 11 Output:01/11/2004--->30/11/2004 on pivot table and regarding data Thanks a lot ...

Automatic Date in Word Document
I need to create a document that I will be sent as an attachment to an email. I want the document to automatically reflect the date the document was sent. I do want to open the document itself. How do I do this? Thank you. -- valerie The only dates that are available to you are 1. A date that you manually enter into the document 2. The date on which the document was created (by use of the { CREATEDATE } field 3. The date that the document was last saved (by use of the { SAVEDATE } field 4. The date upon which the document was printed ( by use of the { PRINTDATE...

Macro to select files with specific dates from a folder
I would appreciate your help to create a macro. I have a folder with a particular date say Aug 2006 and it contains various files with specific dates say 1st August, 2nd August etc and some with different date format. The macro should pick up only those files with dates 1st August, 2nd August and NOT select the files with the different date format and deposit these selected files into a new folder named Aug 2007. I want to make the macro code as generic as possible. Thanks, Amit Check out this page http://www.rondebruin.nl/folder.htm See the macro Copy_Files_Dates -- Regards Ron de Br...

How can I limit size of external emails whilst having no limit on internal?
How can I limit size of external emails whilst having no limit on internal messages? regards Roga http://www.msexchange.org/tutorials/Set-Size-Limits-Messages.html There are a number of places you can set limits. In your case you may just want to set them on the SMTP VS. "roga" <{news2005}REMOVE_THIS_UPPERCASE_2_REPLY@roga.co.uk> wrote in message news:elQ8YfgiGHA.1208@TK2MSFTNGP02.phx.gbl... > How can I limit size of external emails whilst having no limit on internal > messages? > > regards > > Roga > ...

convert text "20100315" to date format
Hi, I have some data exported from a system, the date is a text displayed as "20100315" which should read yyyymmdd. I need to convert this text to another date format showing mm/dd/yyyy in text for importing into another system. As the text format yyyymmdd does not contain any "/" or "-" so using text to column will be a difficulty. Please advise how could I handle this. Thanks a lot. Best Regards, Terry =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm-dd-yyyy") and copy paste special-values. -- Regards Dave Hawle...

Need to value dates
I do a lot of work in cleaning up mailing lists other people have generated. When I get any date column going, I usually =Value() it, to find out if any of the dates are bogus. Sometimes they are real dates, and sometimes they are "08/12/2006" and they get converted and formatted. I am confronted with a list which has the dates as "Aug 12, 2006" and I'm trying to figure how to get them as real dates. Any help would be gratefully appreciated. -- Regards, P D Sterling Dallas TX As long as they're all in the same format that you posted, you can use TTC (Text ...

Converting a formula to a set value
I would like to export some figures that are calculations into another file, but have only the fixed values (i.e. eliminate the forumlas) when I export the data. How is this done? S. Rosenthal napsal(a): > I would like to export some figures that are calculations > into another file, but have only the fixed values (i.e. > eliminate the forumlas) when I export the data. How is > this done? Select cells, copy them and select the target. Use right mouse button and choose Paste special - Values. That's all. Marian Use Copy/Paste Special [Values]. David Hager Excel ...

date format in Excel 2001
I have Office 2001 installed on a 14" iBook running OS9.2.2. I am having a problem with the date format in Excel: my "date and time" control panel is set to use dates in the d/m/yy format, but Excel only recognises dates entered as m/d/yy. How can this be fixed? In article <8b8501c37ee4$9df7c590$a601280a@phx.gbl>, "fgraver" <fgraver@cdnfilmcentre.com> wrote: > I have Office 2001 installed on a 14" iBook running > OS9.2.2. I am having a problem with the date format in > Excel: my "date and time" control panel is set to use dates...

Inserting auto weekly dates
I'm working on timesheets that go from Monday to Sunday, which changes weekly. How can I insert weekly auto dates for Monday to sunday? Hi Marilyn not sure exactly what you mean by "insert weekly auto dates" but one thing that might work for you is assuming Monday's date is in cell B1 and you want Tuesday's date in cell B2, Wednesday's in cell B3 etc, then you can type the date into cell B1, then in cell B2 enter the formula =B1+1, copy this down for Wed -> Sun - now all you have to do each week is type in Monday's date and the rest will change automatically...