Converting Date to Text

I have the following Sql code that I would like to use in Access. What would the syntax be. I tried using Text inplace of convert but have not had any luck.

convert(char(8), bookdate

Sponsored Links:

Related Topics

Converting Integar date to normal date.
I imported some data into an Access table and it is a Long Integer. ex. 20091209. I tried converting the field to a Date/Time and it wouldn't let me. What would be the best way to convert this to 12/09/09? I could create a new field in the table and run an Update query using the Format command possibly?

I tried converting it on the form but no Date options were available


Converting a Text Date String to a Date for Calculations
I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format


Converting text date to number date
I'm currently having trouble with the Cdate(expression). I don't quite understand how to apply it.

I have a table that contains a field titled LD with the following date format: "2011-01-12 11:00", but in text format.

Does anyone know How to eliminate the time and convert the text date into a number date so that I can apply conditional formatting to it?


Converting yyyymmdd dates to serial date number
I have two tables that I want to append to each other.But in one of them the date column has the yyyymmdd format and in the other it is a serial date number.How can I make them consistent by either converting yyyymmdd to date number or by converting the date number to yyyymmdd


converting yyyymmdd dates to serial date number
I have two tables that I want to append to each other.But in one of them the date column has the yyyymmdd format and in the other it is a serial date number.How can I make them consistent by either converting yyyymmdd to date number or by converting the date number to yyyymmdd


Coverting Text (02Jan2011) to Date (01/01/2011)
I am having trouble in converting text 02Jan2011 to date 02/01/2011.
Can anyone tell me how I can conveert a column in a table from text to date. please


Converting Text column with a date/time to a Date column with only the date.
I have a text column with a date/time that loos like the example below.

1/22/2009 12:00:00.000000 AM

I would like to populate a column with just the date in the date format.

1/22/2009

Would CDate work? If so, how would I write it VBA format or SQL (not very familiar with it though)?


Converting text to date
I am importing data from a text file, some of the imported data are dares, written in the following format:
12mar2009

The problem is that the data is stored as text and not as date type of data, which makes it difficult to do mathematical procedures on that type of data
is there a function/way to convert that to "real" date data


Converting text string to Date Format
I imported a file to Access in text format. I now need to convert several fields to date format xx/xx/xxxx.

I'm not familiar with how to write the expression needed to use CDate in an Update Query


Converting a string to date/time
I have a large text file to be imported into Access 2010. Several of the fields are date/time fields. The text file formatting of the date/time is as follows:

20120412092134

I can take this string and convert it so that it looks like this

04/12/2012 09:21:34

but when I use any of the builtin conversion functions I normally use all I actually get is the date the time gets stripped off. Is there a builtin conversion function for this that I'mnot finding or do I need to build one myself?


Converting text digit to datatype "Byte"
In a query I have the birthday month and date. However, I want to sort by date, but it is datatype text so 11 and 12 etc come before 2, 3 and 4. If I could "cast" or change the datatype to Number (probably Byte), then I could sort it correctly. Can anyone advise how to make the text digits act as numbers


Lapse days
in my form I have three text boxes for year, month and days and I have two date field for start date and end date. from the entries of these two date field the difference has to be supplied to the text fields. normally subtracting the start date from the end date will give the result. but what I am facing is that when the difference is in days my idea does not work and the subtraction gives me some thing like the year 1999 and so. if I use DateDiff function then I get the days but converting the days into days, month and year again gives me a wrong difference in dates because of the actual months days. if any body have any idea to resolve this problem then


Date/Time Issue
I am having a issue with some data I imported from a CSV file. First of all the Date/Time field came over as 'Text' data type instead of a 'Date/Time' data type. I tried to change the settings to a Date/Time format while importing but then it would give me an error and the date/time would not load at all.

I need Access to see this as Date/Time for calculations and such, but I am having a hard time converting it.

I have tried in a query using Format and CDate with no luck. Here is what the date/time looks like in the DATA_DATE field.

09-OCT-09 12.00.00.000000000 AM

This time format is new to me and I would rather it be 09/OCT/2009 12:00:00 AM as well as be a date/time datatype. I was thinking I will probably have toparse this "String" of text out using function like Len, Right, Left etc.

Using Access 2010


Converting multiple columns into one row
VBA scripters,

I need help on converting multiple columns into one row (actually two rows).
I have a Access table with 26 columns UnitID, Date, 1, 2, 3, 4,.,24.
(data in column "1" means KWh used in hour 1, and samething appplies to each hour until hour 24)

I want to convert this table to four columns (rows expands to 24 times larger since columns of hours changes to rows)

UnitID, Date, Hour, KWh_used

Table before converting is below

UnitID date 1 2 3 . 24

and, a table structure I want to see after conversion is below

unitID Date Hour KWh

Could you please help me using Visual Basic (in Access 2007) to convert multiple columns of KWh used in each hour into two columns, Hour and KWh used


Converting imported string dates to Date/Time
I am importing a large amount of data from an HTML document into a table. There is a text field with dates in it and the format of the dates is

June 8 12:48:10 2010 EDT

How do I convert this to Date/Time? I have tried a lot of different ways in an update queries with no luck. Any help would be greatly apreciated.


Error converting data type DBTYPE_DBDATE to date
I'm getting this error running an openquery "Error converting data type DBTYPE_DBDATE to date." Exist a workaround to resolve this?


retrieve the missing dates
I am using MS access via OBDC to a DB. A column in a table contains dates, however they are in the format DD/MM/Y and DD/MM/Y HH:MM:SS.

I have a query (main) that calls other queries (sub), and the Main query requires the user to input a From and To date. The results of the query exclude the records that have HH:MM:SS for the To date.

How can I ensure I retrieve the missing dates?

I have tried converting the date to text by the of the Day, Moth, Year function, and then converting it back with DateValue. ie

td: DateValue(Day([add_date]) & "/" & Month([add_date]) & "/" & Year([add_date]))

If I place the date range in the criteria field ie "between #01/01/2012# and #05/01/2012#", the query works fine and the results back are as expected. If I change the criteria to"between [From] and [To]" and then run the query and I put in 01/01/2012 for From and 05/01/2012 for To, I get all records with a value of DD equal to 01, 02, 03, 04, 05, and MMand Y every combination that is in the data ie 01,02,03,04,05,06,07,08,09,11,12 and 2012, 2011, 2010 etc


Converting Text to Time - hh:nn
I have been struggling with converting my "start_time" field that is stored as text to a time format of "hh:nn" in my query.

In my query I put the following expression to try and convert from text to time value, but when I do the query reflects 00:00 for all records.

Expression: start_time2: Format(CDate([start_time]*1),"hh:nn")

I attempted to change the field to a number by multiplying the field by 1.


Converting date field in numbers
I need to convert a date to a number that could be reconverted again in the original date, and -here's the point- I need to convert it to 0 if it's blank.


Updating a date text box from a text box
I've got a form with a text box that has a date field that the user has to pick a date. I also have a combo box on the same form with different lengths of time in there the user also chooseseg 6 months 1 year 2 years etc.
What I want to happen is the third date field text box To look at the first date filed text box add the period from the combo box and automatically update the third date text box.