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
1550 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:

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Is there a registry key to specify the idle time before tracking info is processed?
Hello, In Outlook, read receipts and delivery notifications are processed in idle time, which seems to be about 1-2 minutes. Anyone know if a registry key specifies the exact idle time? We're looking to reduce it, like to zero if feasible! Thanks, - Alan. How did you come to this conclusion? Receipts are processed immediately = when using Exchange. Perhaps you need to specify your environment. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, Alan asked: | Hello, |=20 | In Outlook, read receipts and de...

Odd date problem
I'm reposting this as this is a very strange problem in Access. I'm using 2003. After my entry is an entry by missinglinq via AccessMonster.com who was able to reproduce the error. Looking forward to comments/fixes. Thanks, Rob I have the following in the DblClick event for the control CaseStart which is a date/time field. The problem is that once the control is double clicked the form remains in edit mode, even though I've told it not be editable. Additionally, when the control is double clicked, it shoudn't enter in the time until the Edit button has been clicked o...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Appointment
I seem to have a recurring problem with CRM appointments in CRM 3.0, I have a callout which creates/updates an appointment, the problem is that for example when I create an appointment with an End Time of 10/06/09 it shows in the form when viewed in CRM as 09/06/09, even though when I look at the database table the correct date is being held (10/06/09)? This problem seems to occur no matter what date is entered? I have also ensured the duration is correct (e.g. all day event with correct number of days, e.g. Start = 06/06/09 - End = 10/06/09, Duration = 5 days), I'm puzzled as to what else...

how to convert excel's .cvf file to .csv file
...

why I see times new roman?
I have several html email messages composed in outlook express 6, I'm sure I highlited all text and set it to Arial 10. Then I drag the file to desktop, move it to vista windows mail draggin into inbox or other folder. The result is some parts of text shows arial 10 but some others Times new roman 10 or 12 what is annoying, because I use all arial 10, so I have to manually forward the email, highligh it again and set arial 10 and then, yes, I can see it properly. I checked the compose default font and everything is fine. Why is that? Is there any work around , helo...

Is it possible to customize the Billable time increments?
Hi all, Does anyone know if it is possible to customize the billable time dropdown in the Resovle Case page? Our company bills on 15 minute intervals and when the time goes about an hour, it starts jumping by the half hour. Is this data modifiable from anywhere in the database? Any help is appreciated Thanks Josh ...

Dates not correct
How do I get my DateDif output to reflect the correct 30 days in a month. Im getting 49 days, it should be pulled to the months and 19 days in days. 2 years, 11 months, 49 days See this for examples: http://cpearson.com/excel/datedif.htm Biff <belczyk@gmail.com> wrote in message news:1177357276.085439.98020@e65g2000hsc.googlegroups.com... > How do I get my DateDif output to reflect the correct 30 days in a > month. Im getting 49 days, it should be pulled to the months and 19 > days in days. > > > 2 years, 11 months, 49 days > ...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Outbox displays Received Date
Need some help on this one. I'm using Outlook 2000 on a Windows XP machine and for some odd reason the Outbox now displays the "Received" date and the Inbox displays the "Sent" date. How do i correct this. It seems to be hidden in the PST file because when I login on a different machine it shows the same thing. Right click on the column headers and choose the "Field Chooser". Drag the field you want to see from the Field Chooser into the column headers area. Drag column headers you don't want to see away from the column headers. "Lee" ...

Invoice Due Date #2
Hello, I'm using Money Small Business 2006 and need some help. How can I specify the due date on a invoice? It seems to automatically use the date that the invoice is created on. All help appreciated. Thank you. > How can I specify the due date on a invoice? It seems to automatically > use the date that the invoice is created on. Below the Line Item table there is a link "To add or edit billing and shipping addresses, notes, or other information, go to the Invoice Details". Click that link and you can specify the Payment Terms. I don't see where to specify a cet...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

inserting current time absolute value
hey everyone I'm trying to create a formula that when something is entered in one cell the current time is stamped in another cell. So far here's what I came up in my C4 cell =IF(A4<>"", NOW()-TODAY(), "") When something is entered in cell A4, the current time enters in at cell C4. I copied the formula by using fill down, but when I enter in something in the next cell of A5, cell C5 and C4 gets updated with the same current time, when i enter the something in A6, cells C6, C5, C4 gets updated with the same time and ETC, ETC How would I use absolute valu...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

Problem converting from Quicken to M2005
My Quicken files are mostly investment related, and generally converted fine. However all bonds (regular and muni's) converted as Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) How do you change the Investment Type for an item? Thank you. In microsoft.public.money, Mike wrote: >My Quicken files are mostly investment related, and generally >converted fine. However all bonds (regular and muni's) converted as >Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) Money typically converts custom data types from Quicken into funds. I thou...

Numbers converting to decimal
I a trying to figure out why when I type 11 and automatically converts it to .11, if I type 11. it will stay 11,if I change all the cells to text then back to number they willstay. I have checked the formatting of the cells, it even happens when I open a brand new worksheet. Any ideas? Thanks Dawn Hi Dawn, Tools>Option>Edit, uncheck Fixed Decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "DawnP" <anonymous@discussions.microsoft.com> wrote in message news:c3cf01c48a05$d75359d0$a501280a@phx.gbl... > I a trying to figure out why when I type 11 and &...

Importing Entourage Date
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop The board on my old MacBook has just died but I managed to clone the disc and drag all the data out and onto an external drive. All my data is there along with what seems to be the complete system however I can't import the Entourage files, database and folders into my iMac! I've tried to locate the folders and files using import then searching through the Documents / Office 2008 identities etc. but when I get to the database folders they are 'greyed' out and I can't attach th...

Converting Quicken 2004 to Money
Quicken 2004 has many bugs, and I have had it. The most recent being that it doesn't work AT ALL now that it is the year 2004. I have had to change the date on my computer today to open it. I want to get Money instead, however I do not know if Money can get my data from the 2004 version. Does anybody know for sure? Yes is the answer to the question you posed. No is the answer to the question you are getting to but didn't pose. M04 imports Q03 and earlier. If the past predicts the future M05 will import Q04. "Colin" <anonymous@discussions.microsoft.com> wrote ...

Chart in time phase
I need to develop a line chart which includes 2 columns of data. Say for instance the data looks as follows: 5 6 15 9 20 12 50 25 Columns with the 5 and the 6 are for March. Columns with the 15 and the 9 are for February. So as we go up, the month increases. When I make my graph, the 5 and the 6 are first plotted points, the 15 and the 9 are the second, etc. In other words, it is plotting March, February, January, instead of January, February, March. How can I make the chart plot in a logical time phased manner? That is, Jan, Feb, Mar, etc? Thanks. jzis Instead of plain text &qu...

Converting from AOL to Outlook
Is there an efficient way for me to convert my AOL address book over to Outlook? Before I buy Outlook, I want to be sure I cam make the conversion without loosing the large address book I have on AOL. "Herb Clader" <Herb Clader@discussions.microsoft.com> wrote in message news:44A9739C-A94F-49AB-BF08-31D78A86CCB7@microsoft.com... > Is there an efficient way for me to convert my AOL address book over to > Outlook? > > Before I buy Outlook, I want to be sure I cam make the conversion without > loosing the large address book I have on AOL. AOL's help p...