CONVERT TIME & TRIM DATE

I need help converting time to an AM/PM time format
our database displays time in 4 digits : 0006, 0737, 1217, 2149.  I am
finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc.
The date displays: 2007-03-09 00:00:00.000, how do I have it display the date
as 03-09-2007?

Thanks.

0
RENEE705
3/21/2007 7:23:02 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
1813 Views

Similar Articles

[PageSpeed] 24

Specify the display format of the control or field: mm-dd-yyyy

RENEE705 wrote:
>I need help converting time to an AM/PM time format
>our database displays time in 4 digits : 0006, 0737, 1217, 2149.  I am
>finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc.
>The date displays: 2007-03-09 00:00:00.000, how do I have it display the date
>as 03-09-2007?
>
>Thanks.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200703/1

0
kingston
3/21/2007 9:00:14 PM
Data type: datetime (mm-dd-yyyy 12:00:00 AM) I only want to display the date
not the time stamp so just mm-dd-yyyy

kingston wrote:
>Specify the display format of the control or field: mm-dd-yyyy
>
>>I need help converting time to an AM/PM time format
>>our database displays time in 4 digits : 0006, 0737, 1217, 2149.  I am
>[quoted text clipped - 3 lines]
>>
>>Thanks.

0
RENEE705
3/21/2007 9:40:58 PM
On Wed, 21 Mar 2007 19:23:02 GMT, "RENEE705" <u32675@uwe> wrote:

>I need help converting time to an AM/PM time format
>our database displays time in 4 digits : 0006, 0737, 1217, 2149.  I am
>finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc.
>The date displays: 2007-03-09 00:00:00.000, how do I have it display the date
>as 03-09-2007?
>
>Thanks.

What are the datatypes of these fields? Text? Are these tables linked from
some other database system, or native Access tables?

             John W. Vinson [MVP]
0
John
3/21/2007 9:59:13 PM
I am in the SQL Server 2005 using the SQL Server Management Studio pulling
from dbo tables from the Ecometry Software program we use in our business.
The datatype is DATETIME format, it looks to be text.

John W. Vinson wrote:
>>I need help converting time to an AM/PM time format
>>our database displays time in 4 digits : 0006, 0737, 1217, 2149.  I am
>[quoted text clipped - 3 lines]
>>
>>Thanks.
>
>What are the datatypes of these fields? Text? Are these tables linked from
>some other database system, or native Access tables?
>
>             John W. Vinson [MVP]

0
RENEE705
3/21/2007 10:25:11 PM
On Wed, 21 Mar 2007 22:25:11 GMT, "RENEE705" <u32675@uwe> wrote:

>I am in the SQL Server 2005 using the SQL Server Management Studio pulling
>from dbo tables from the Ecometry Software program we use in our business.

Then I'd suggest posting your question in a SQL/Server newsgroup; this
newsgroup is for Microsoft Access queries. Access has a rather different way
of handling date/time data!

             John W. Vinson [MVP]
0
John
3/21/2007 11:20:11 PM
On Mar 21, 11:20 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> Then I'd suggest posting your question in a SQL/Server newsgroup; this
> newsgroup is for Microsoft Access queries. Access has a rather different way
> of handlingdate/timedata!

Not greatly different, IMO. For example, SQL Server dates are also
based on double float values; certainly they are treated differently
(with more respect for strongly-typed data?) e.g. you don't often  see
people doing my_date + 1 in SQL Server land, even though the behaviour
is the same as for Access/Jet.

Jamie.

--


0
Jamie
3/22/2007 10:59:05 AM
Reply:

Similar Artilces:

Converting a Microsoft Word document to a PC document
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have created a Resume and a cover letter in Microsoft Word on a Mac to be uploaded to the Institute ORACLE recruitment system. <br><br>When I checked how it looked, the format, the bullets and formating were blown apart! I was told to convert the documents to PC format. <br><br>I don't know how to do this. I got some guidance to make them PDF files, but the formating still goes haywire! How do I do the conversion on my Mac? Hi Meg: The most important thing to do is DO NOT TEL...

Money Changes Transaction Dates
Hi all, How can I stop Money2004 from changing my transaction dates that I have already entered into the register? This happens when I accept/match downloaded transactions from my bank. Thanks! Well, I think I just answered my own question. I found a check box to untick under online options to fix this, but I will post back if necessary. "rustyfender04" <rustyfender1@hotmail.com> wrote in message news:eY3zaLITHHA.2124@TK2MSFTNGP06.phx.gbl... > Hi all, > > How can I stop Money2004 from changing my transaction dates that I have > already entered into t...

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

time compare
i need an example of how to code a time comparison. i need to check to see if a value in a date/time column is greater than 10 minutes from the current time. i appreciate your help. thanks, mcnewsxp On Fri, 8 Oct 2010 09:25:43 -0400, "Michael Courter" <mcourter@mindspring.com> wrote: >i need an example of how to code a time comparison. >i need to check to see if a value in a date/time column is greater than 10 >minutes from the current time. >i appreciate your help. >thanks, >mcnewsxp > Difftime() -ralph Michael Courter wrot...

Convert date to months
I have a database that has multiple dates in it. What I am trying to do is in one field I have a date and I am want that date to convert to months in another field. Can anyone help me on this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 are you trying to find months between dates? or convert 4 to April? Look at format function for ways to display months, datediff function for months difference.. "bohon79 via AccessMonster.com" <u35329@uwe> wrote in message news:74ac2f3f40ce9@uwe... >I have a database ...

How do you add five seconds to a series of times?
I have a column for of dates and times, I need to create a function that will allow me to add 5 seconds to the time so I can fill down. time and dates are kept in fractions of a day therefore to add 5 seconds add 5/(24*60*60) to the previous cell "Redhead" wrote: > I have a column for of dates and times, I need to create a function that will > allow me to add 5 seconds to the time so I can fill down. ...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Processes Requiring Lengthy Amounts of Time
Hello: I have several clients with above average databases (sizes greater than 15 GB and up to about 40 GB). When they need to run checklinks, reconciles, and/or year end close processes, it takes forever. Depending on which process, it could take 15 to 30 each to process. That is really tough when you have a business that cannot afford that type of downtime. Now, both of the companies that I am thinking of have hardware that is well within recommended specs based on the MBS hardware recommendations. The memory does not spike to an extreme, nor does activity with the CPUs, but har...

How to add 5 min to current time ()
Greeting, How to add 5 min to current time ()?? On Tue, 12 Feb 2008 21:23:00 -0800, ghost <ghost@discussions.microsoft.com> wrote: >Greeting, >How to add 5 min to current time ()?? DateAdd("n", 5, Now()) or for just a time value - actually a date/time value on December 30, 1899 - DateAdd("n", 5, Time()) -- John W. Vinson [MVP] ...

Need to convert point on screen to various screen resolutions
Let's say you click on a button on your screen at 1000,2500 TWIPS and your resolution is 800 X 600. Now you change your screen resolution to 1024 X 768 and you need to click on the same button in it's new location on the screen. Who's 100 times smarter than I am and can do some tricky math that will tell me the TWIPS to find that button? I'd ned to do the same calculation for other screen resolutions like 640 X 480 etc. http://www.applecore99.com/api/api012.asp -- Regards, Tom Ogilvy "Donna YaWanna" <diy@mdahospital.com> wrote in message news:%23HMLEWW2...

Inconsistant meeting times
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Email Client: Exchange I am currently experiencing issues with meeting times coming in wrong. Colleagues on PC's (using Outlook) invite me to meetings and the invite says one time (ie: 10:00am) and when I accept, it appears on my calendar as 9:00 and the notify pops up for 9:00 as well. <br><br>When I set up my own meetings there is no time difference which is making things very confusing. <br><br>This has only been an issue for the last month or so, and our IT department cannot seem to track down a fix. ...

How do I convert a list to an Excel file?
I have a WORD file with 48 lines of comma delimited data in the form: xxx,xxxxxxx,xxxxxx,x,x,xxx,xxxxxxxxx xx,xxx,xxx,xxx,xxx,xx,x I would like to convert the WORD list to EXCEL. When I attempt to open the WORD file in EXCEL, I thought a conversion window would appear....what I actually get is "incorrect format" Hi! Save the WORD file as a plain text file. Then, when you open the file in Excel a Wizard will open and step you through it. Just select COMMA as the delimiter. Biff >-----Original Message----- >I have a WORD file with 48 lines of comma delimited data in ...

Calendar/Dates Help
Hi All, I have an excel spreadsheet that lists every date in the year, with a particular code in the next cell. IE: Monday 3/01/2005 11M 22M 32M Tuesday 4/01/2005 11T 22T 32T Wednes 5/01/2005 11W 22W 32W Thursday 6/01/2005 11H 22H 32H Friday 7/01/2005 11F 22F 32F Saturday 8/01/2005 11S 22S 32S Sunday 9/01/2005 11N 22N 32N Monday 10/01/2005 11M 21M 33M Tuesday 11/01/2005 11T 21T 33T Wednes 12/01/2005 11W 21W 33W Thursday 13/01/2005 11H 21H 33H Friday 14/01/2005 11F 21F 33F What I need is to be able to search by the code eg "33T" and have all the dates listed for ...

Date comparison better method
Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and '2010-06-17 23:59:59.997' Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND DateColumn < '2010-06-17 23:59:59.997' I am seeing in a project both the above methods of data range filering is happening in different SPs. I am trying to understand which is the better method of comparing two date values and why? [Btw i know BETWEEN considers both the upper and lower limit] Regards Pradeep I would say the following is the better approach: ...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

Length of time
I am trying to keep track of time spent on a job. I put in the start time and the stop time. Individual day tallies are fine, but when I try to get a total I have 00:50 right now. How can I go from clock time to length of time? Thanks -- Mike D. www.stopassaultnow.org Remove .spamnot to respond by email --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 8/19/2003 Hi Format the cell as Custom "[h]:mm" Arvi Laanemets "Mike Dobony" <tkdchap@hotmailspam...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Out of Office Assistant replies multiple times
The situation: The user of a fully patched Outlook 2003 SP1 sets an Out of Office reply using the Out Of Office Assistant.. The result: The first time someone sends a message while the OOA reply is turned on, they get the full text of the OOA reply as it is configured. The second and subsequent emails from that same sender gets a truncated version of the OOA message. In my testing, my second message (which isn't supposed to be replied to), got me 2 truncated replies. Examples from my testing: 1st reply: "I am out of the office and will return on Monday, February 28, 2005. At th...

How do I sort data by date excluding time
I would like to sort a database by date and transaction type. The problem is the data sorts by time, even though the field is formatted to show date only, with the transaction type not sorting within the date because it shows up in the time order. How do I get rid of the time? If date/time column is A then you have to use an empty helper column filled with formula =INT(A2) and sort by it. -- Regards! Stefi „markd” ezt írta: > I would like to sort a database by date and transaction type. The problem is > the data sorts by time, even though the field is for...

Editing more than 1 table at a time with SQL.
Say I have two tables in a DB called TABLE1, and TABLE2. TABLE1 [IDNUMBER] [FIRSTNAME] 1 Ryan 2 Jennifer 3 Monica TABLE2 [IDNUMBER] [LASTNAME] 1 Johnson 2 Snipes 3 Smith My question is would I be able to use SQL in code to Edit, Delete or Insert data to both tables in one string rather than one table at a time? For instance what if I want to change both tables with a [IDNUMBER] = 2 to a [IDNUMBER] = 4, OR change the ...

Working with elapsed time data
How do I set up Excel to accept race times for a cross- country team? I want to enter minutes and seconds for each runner for each race. Then I want to calculate each runner's average time. When I enter minutes and seconds, Excel converts it to the time of day - which is not what I want. Go to www.cpearson.com for lots of tips on working with time. Bernard "DW" <dweskalnies@vertisinc.com> wrote in message news:080f01c38854$46e18100$a401280a@phx.gbl... > How do I set up Excel to accept race times for a cross- > country team? I want to enter minutes and seconds for...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

Prevent excel from changing numeric data ranges into dates?
I want to prevent excel from changing my numeric data into dates (ie. 10-12, into 12-Oct). I want to import specific data ranges, not a date values. Whenever I paste or type a possible date, like 10-12, excel assumes I am writing Oct. 12, 2004. Then, Excel formats the cell into a Custom d-mmm format (see scrn shot @ www.vd4.org/excel_issue.gif). If I change the Custom format to General, excel converts the date into the DATE'S value (ie. 12-Oct, into 38272; which I think is the # of days since 1900?). How can I *prevent* excel from changing my data ranges into dates (ie keep 10-12 ...

Convert litres per 100km to miles per gallon easily
I need to be able to set up a spreadsheet that allows users to type in a figure for Litres per 100km (fuel consumption measure) and translate it into a miles per gallon (UK measures) figure (fuel economy measure). ok here is a long way to do it say cell A1 is where you type your figure of l/100 k in some other cell type this 100/A1*4.55*0.62 4.55 l per uk gallon.0.62 kilometer =mile..... every time you type a figure in A1 the MPG displays in your other cell...... easy one to check me on is 10 l 100 ks,= 10 km litre =28.2 mpg -- paul remove nospam for email addy! "Ann Cardus" ...

Counting dates
I am trying to count the number of dates in a column that appear b month and year. For instance, I some that are 11/23/2003 and 11/24/200 and 11/21/2002. How can I makew a formula that will count the instance of 11/2003 -- Message posted from http://www.ExcelForum.com =SUMPRODUCT((YEAR(A1:A100)=2004)*(MONTH(A1:A100)=11)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jdavis3874 >" <<jdavis3874.14770b@excelforum-nospam.com> wrote in message news:jdavis3874.14770b@excelforum-nos...