How to convert data/time to string in query?

How to convert date/time to string in query? So I can handle it with using 
"Left" or "Right" function.

Thanks for any help in advance!
0
Utf
7/27/2005 10:01:02 PM
access.conversion 3037 articles. 0 followers. Follow

2 Replies
635 Views

Similar Articles

[PageSpeed] 23

You can do that with the Format function. However, if your purpose is to 
extract a part of the date, you can do that in a more locale-independent way 
using the various functions designed for that purpose, such as Year(), 
Month(), Day(), etc.

Here's an example that on my system (where short date format includes 
four-digit years) returns the same value in both columns (albeit one is a 
string and the other is an integer). The first column might return a 
different result on another system, the second column would not ...

SELECT Right$(Format$([OrderDate],"Short Date"),4) AS StringDate, 
Year([OrderDate]) AS OrderYear
FROM dbo_Orders;

-- 
Brendan Reynolds (MVP)

"Frank Xia" <FrankXia@discussions.microsoft.com> wrote in message 
news:E224A9CD-0DA7-44B4-9D44-286031B0F858@microsoft.com...
> How to convert date/time to string in query? So I can handle it with using
> "Left" or "Right" function.
>
> Thanks for any help in advance! 


0
Brendan
7/27/2005 10:37:59 PM
Thanks for your help. That is what I need.

"Brendan Reynolds" wrote:

> You can do that with the Format function. However, if your purpose is to 
> extract a part of the date, you can do that in a more locale-independent way 
> using the various functions designed for that purpose, such as Year(), 
> Month(), Day(), etc.
> 
> Here's an example that on my system (where short date format includes 
> four-digit years) returns the same value in both columns (albeit one is a 
> string and the other is an integer). The first column might return a 
> different result on another system, the second column would not ...
> 
> SELECT Right$(Format$([OrderDate],"Short Date"),4) AS StringDate, 
> Year([OrderDate]) AS OrderYear
> FROM dbo_Orders;
> 
> -- 
> Brendan Reynolds (MVP)
> 
> "Frank Xia" <FrankXia@discussions.microsoft.com> wrote in message 
> news:E224A9CD-0DA7-44B4-9D44-286031B0F858@microsoft.com...
> > How to convert date/time to string in query? So I can handle it with using
> > "Left" or "Right" function.
> >
> > Thanks for any help in advance! 
> 
> 
> 
0
Utf
7/28/2005 4:58:02 PM
Reply:

Similar Artilces:

MSFlexGrig and SQL Query
How can i load dates in a MSFlexGrid Object whit a SQL Query???? ...

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

Email Activity Page takes time to close
Hi, In my application, if the email content in an email activity page is large, the page takes 15-20 seconds to close. I think I should dispose objects while closing the page. But I don't know where to do it? Help please ...

Mapping Geographic Data
I want to create a data map and I need to have Microsoft Map installed. Apparently Microsoft Map was not installed when our IT Department installed Excel (I am running Excel 2003). I believe I ahve to rerun Setup to install Microsoft Map however my IT Department says they can't find Microsoft Map in the Setup file. Can any one help me tell them how to locate Microsoft Map in the Setup file? Hi, Map was dropped as a free add on to excel after xl2000, I think that's the version. Anyhow to get Maps in xl2003 you will need to buy the standard alone program. Cheers Andy Mike wro...

Linked cells, make the data go hard manually
I'd like to convert a cell linked to a previous day to hard numbers once a day. Is that possible? By 'hard number' you mean one that is no longer linked, you could copy, then paste as, paste value. HTH, Carole O "Johnny" wrote: > I'd like to convert a cell linked to a previous day to hard numbers once a > day. Is that possible? ...

Subform Data Entry Problems
I have a subform based on a query linked to the Form by "ContactID". The query works fine when run independent of the subform. I am able to enter all data fields and where necessary the autonumber function assigns properly. But when I try to use the subform all records related by my table "tblADDRESS" do not allow data entry (the fields are not locked). I imagine the ContactID Master/Child link in some way is interfering with the query in Form view, but I've tried every variation of join properties within the Query for the Subform and in the Query for the main...

Printing Autofilter Data
How do I print Autofilter data onto one sheet instead of a few lines on say 20 pages? In page Setup, select fit to print and select 1 page wide and 1 page high. -- Regards, Tom Ogilvy "Confused" <Confused@discussions.microsoft.com> wrote in message news:2C7DCB67-06B2-429C-99D9-D2B3A40FEF4A@microsoft.com... > How do I print Autofilter data onto one sheet instead of a few lines on say > 20 pages? Thank you! "Tom Ogilvy" wrote: > In page Setup, select fit to print and select 1 page wide and 1 page high. > > -- > Regards, > Tom Ogilvy >...

Parameter Query using date ranges
I am creating a parameter query where I want to pull data between 2 dates. In the date criteria of my query, if I write between #01/01/2008# and #01/31/2008# +1, I will get all data for January. If I use a form to enter the dates and have Between [Forms]![autoexec]![start] And [Forms]![autoexec]![end] +1 in the date criteria, I get an error. Does anyone know what I should put in the criteria to make the query work? Are you entering your dates as m/d/y? What happens if you try: DateAdd("d", 1, [Forms]![autoexec]![end]) Is the form open? -- Duane Hookom Microsoft Access...

How to convert a Publisher document for blind readers?
We're a church that uses Publisher to produce a weekly 4-page booklet that we print and distribute on Sunday. I'd like to email the document in advance to some blind congregants who have a text reader on their computer at home. When I convert the doc to plain text, the order of the text gets messed up. How can I convert the Publisher doc to a format easily read by text readers for the blind while maintaining the correct order of words? Thanks in advance for your help. Can the readers they use read .pdf files? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Mic...

Outlook times out when receiving
Outlook 2000 SP3 has been working just fine on a WinXP SP2 system. Now it sends ok, but times out when trying to receive. All the settings are the same as they have been. Outlook 2000 email works fine with an older Win98 system using the same settings, so its not the server. Any ideas? What error message do you get? Do you use a virusscanner that scans incoming mail? If so; disable the incoming mail scan and see if it still happens. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed w...

Convert ASCII to Hex
Hi, I'm not very proficient in Excel, so this may not even be possible.... I do network administration and often find myself having to type in a WEP key in HEX which, especially on a PDA, can be a time consuming task. I was looking to have an excel spreadsheet (pocket excel to be specific) that can convert the ASCII text in one cell to HEX in another. A great "extra" bonus would be that you HAVE to use either 13 or 26 characters. Anyone know of a function that could accomplish this? Thanks so much, STS On Tue, 26 Apr 2005 10:02:14 -0400, "Scott Sanford" <sc...

&quot;New Web Query&quot; function
good day, are there any friends use New Web Query fuction under Data -> impor external data? My objective is to export (a list of stocks info) from web page t Excel spread sheet. e.g. the is is Microsoft's stock details (stock code: MSFT) http://finance.yahoo.com/q/ae?s=MSFT i can use "New Web Query" function to link this page to spread sheet. Question: how can I define a list of stocks (let say 10 differen stocks) that I want to monitor apart from creating 10 differen separate spreadsheets link with each individual stock code? any ideas are welcome thanks for your help ...

amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...

Automatically pull data from one file to populate a template
Hi Everyone... Just beginning to get the hang of vba coding in excel. I would like to implement a new feature in a spreadsheet here at the office. We currently have a master records sheet containing vital information on unit processing. Each unit then has a sub file containing additional information. Currently one must populate the master record file. Then open a template and populate another file with the same information. I would like to streamline this process and have the data in the master record file automatically inserted into the template, allowing the user to only have to ...

converting to pdf and losing things
I have pub 2003, i am creating a document and then converting it to pdf with primo pdf. When it opens up in adobe, some of my text boxes are gone from my original document and I dont know why or how to get them back, I have tried changing them to a simple font like Times New roman, but they are still not showing up. IM on a deadline, someone pls help! Thanks Don't know what to tell you. Download a different converter, see it that cures the problem. There are many. http://www.google.com/search?hl=en&q=pdf+converter&btnG=Google+Search If all fails send me the file and I will co...

Converting TOC to Text
I cut and paste text from a table of contents into a Word 2003 document. It is very lengthy and I'd rather not re-type the whole long list, since it would take a very long time. However, the TOC formatting is still in the document and I can't get rid of it. I can't include hyperlinks or change font color as a result. I've tried clearing all formatting without effect. Anyone know how to convert a TOC into text? Thanks! Ctrl+Shift+F9 will unlink it. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org &q...

SQL Server (XML Data) Through ODBC to MS Access
Hello, In one database in my company. Previously the data in SQL Server, the tables were simple text and number formats and it was easy for me to link the data through ODBC to MS Access and I could do all sorts of queries and calculations, etc. But, now (after several months) I realize that the DBA and Programmer changed, the data in SQL Server to be XML Data Type in the new setup. In SQL Server. dbo.FlightDocuments Table docID, int docInfo, xml docLogs, xml and so on. and I see the data in Access (through ODBC) for one of the fields of the above table like this: ...

Using SQL Query to mass update a field.
Having a problem in eOrder where the pictures are not showing up. I found out the Inventory image in internet information is mapped to a drive letter. If I remove the drive letter, I can see the picture in eOrder. I would like to mass update the SY01200 INET4 field and change all from f:\inventory pictures\image.jpg to image.jpg (where image.jpg is the file name). It sounds like I need to create a cursor script but am not sure how to do this. Can anyone help me with this? Thanks. Backup you data first. You might be able to use DTS to trim the data with a string transformation. Or you ...

Save data in a worksheet in fixed length fields...
I have data in a worksheet (Office 2000) which I would like to export in fixed length format for importing into another application. Is there a way to use column settings or a wizard to export the file in correct format rather than exporting and then having to do signficant editing before I can import the file into the other application? one way: http://www.mcgimpsey.com/excel/textfiles.html#fixedfield In article <9C97DB7F-65EB-4438-926E-23906D56E75D@microsoft.com>, dietzd <dietzd@discussions.microsoft.com> wrote: > I have data in a worksheet (Office 2000) which ...

I want to enter a customer # and retreive their data
How do I retreive data, which I've entered, from another worksheet when I enter a code or # that is specific to that data? Assuming you have a yable and the data is set up like code1 value value etc code2 value value etc and so on then you can use =VLOOKUP(C1,Sheet2!A2:C50,2,0) where C1 is the typed in lookup value (code) and A2:C50 the table and this particular formula will return the value from column 2 lookup VLOOKUP in help -- Regards, Peo Sjoblom Nothwest Excel Solutions www.nwexcelsolutions.com remove ^^ from email "It is a good thing to fol...

Advice on timing of posts appearing #3
I've noticed over the last few days that some of the posts I've made are taking a very long time to appear. Last night I replied to mevett's post "Link to a changing cell? You'll see ..." at 11:58pm (my time). Over an hour later it hadn't appeared, so I posted another one (1:10am). This morning both were visible (though with the second one appearing before the first), so I posed a question then (about 9:20am) on why this should be so - this still hasn't appeared, and it is now 6:40pm. During the last 90 minutes or so I have replied to a number of other posts i...

Aggregating Data in Fifteen Unopened Spreadsheets into Single Consolidated Sheet
I have a series of 15 spreadsheets, all having the same column structure. They all have the same filename barring first two letters which refer to a persons initials. The 15 files are stored in the same directory. In that directory I'd like to create a sixteenth file which consolidates much of the data in the 15 files. I want to use that consolidated data in a pivot table. I know that the top-left- hand-corner of the range I want within each of the 15 spreadsheet files is the Cell A10. I cannot be so precise with the bottom right hand corner (BRHC) as the number of rows is variable i...

A good program for keeping track of time...ie outlook timecard add on
Is there a good program for keeping time for jobs. I've used the appointment as a means for doing this for years, but this can be quite cumbersome as workflow gets more complicated. Looking for the next best method. I use outlook 2007 and quickbooks 2009. I've currently manually transfer info from outlook to quickbooks, then do billing. Any suggestions. I'm looking at timetrax, but interested in other input. Thanks. ...

How to organize data?
I am a frustrated Excel newbie. I grit my teeth every time I have to use Excel. But I need to create a small database of records and chart it's monthly progression, and I think Excel is probably the best program for the job. Only, I don't know who to do this. Please bear with me as I really need help with this. Say that I have a short list of vendors in different cities. For each vendor I have a total # of items in their inventory and another # of the # of items sold from the inventory, and a percentage of that. So I have the vendors listed in Column A. # of items in inventory ...

Data Normalization
Greetings, I am not sure which newsgroup to ask this question. Any directions would be most appreciated. I have a list of about 5000 customers over a 30 year period. I am trying to give each customer a unique ID. Some of these customers have moved a few times, some of them have gotten married a few times (we deal with the wives primarily), some have multiple service addresses. This question has plagued me for the last 30 years!!!! Any suggestions would be most welcome. TIA -Minitman Hi probably better asked in a database related newsgroup. What you're asking for is quite typic...