By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Text to Date conversion

P: 65
Hi I've got this query:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Aerosol ( dkey, eadnr, eersteZendtijd, VerslagNr, cnr, TypeToestel, Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, Stop )
  2. SELECT dkey, eadnr, eersteZendtijd, VerslagNr, cnr, [Type Toestel], Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, einde
  3. FROM ExportAerosol
  4. WHERE omit="i";
In this query the field eersteZendtijd is selected from a table where it is stored in text. It has to be inserted in another table where the field eersteZendtijd is a date field. The format of the date in the textfield is like: 2006-10-03 09:26:00.0. I would like to select the year, the month and the day from this field to use the function DateSerial. Could somebody help me with this because I can't find the solution.

I got to something like this but it doesn't work:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Aerosol ( dkey, eadnr, eersteZendtijd, VerslagNr, cnr, TypeToestel, Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, Stop )
  2. SELECT dkey, eadnr, DateSerial(Left(eersteZendtijd,4),Mid(eersteZendtijd,5,7),Mid(eersteZendtijd,8,10)), VerslagNr, cnr, [Type Toestel], Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, einde
  3. FROM ExportAerosol
  4. WHERE omit="i";
  5.  
Greetz
Twanne

I've got a bunch of problems, but you ain't one of them.
Sep 10 '07 #1
Share this Question
Share on Google+
3 Replies


P: 65
I've found the solution.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Aerosol ( dkey, eadnr, eersteZendtijd, VerslagNr, cnr, TypeToestel, Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, Stop )
  2. SELECT dkey, eadnr,DateSerial(Left(eersteZendtijd,4),Mid(eersteZendtijd,6,2),Mid(eersteZendtijd,9,2)) as eersteZendtijd, VerslagNr, cnr, [Type Toestel], Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, einde
  3. FROM ExportAerosol
  4. WHERE omit="i";
  5.  
Greetz

Smell nice, smell good, just smell :p
Sep 10 '07 #2

P: 2
I've found the solution.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Aerosol ( dkey, eadnr, eersteZendtijd, VerslagNr, cnr, TypeToestel, Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, Stop )
  2. SELECT dkey, eadnr,DateSerial(Left(eersteZendtijd,4),Mid(eersteZendtijd,6,2),Mid(eersteZendtijd,9,2)) as eersteZendtijd, VerslagNr, cnr, [Type Toestel], Hulpstuk, Medicatie, Frequentie, Onderhoud, Start, einde
  3. FROM ExportAerosol
  4. WHERE omit="i";
  5.  
Greetz

Smell nice, smell good, just smell :p
Hi,

I could not understand the above terminology, can you please put it in access terminology as this is the query I am actually looking for..

thanks alot for your help
Sep 20 '07 #3

P: 65
DateSerial(Left(eersteZendtijd,4),Mid(eersteZendti jd,6,2),Mid(eersteZendtijd,9,2)) as eersteZendtijd

Well this is the function DateSerial( year, month, day)
The value of the field eersteZendtijd is constant yyyy-mm-dd.
So I use Left to get the year from the field (four first characters), Mid to get the month and the day (Mid(Field ,Startposition ,Length to get ).

The function DateSerial makes sure my dates are formatted in the same way over and over again. Because for some reason access doesn't handle dates that well.

Greetz
Twanne

Don't fall, just drop death
Sep 21 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.