Date Conversion 1

[Freckles]

From a Unix server, I get a date that is an integer formatted "yyyymo" (EX: 200101). I need to turn this into a "real" date so that a crosstab query and/or a chart gets the correct column order.

I tried format([unixdate],"yyyy-mm&quot and got "244711" no matter what the [unixdate] was.

Can someone please advise the a way to do this?

Deb Koplen
deb.koplen@telops.gte.com

A person can stand almost anything except a succession of ordinary days.

 

[Nebuchednezzar]

Two things you can do...
1. SELECT LEFT("200101",4) &"-" & RIGHT("200101",2) AS YOURDATE
FROM your_table;
--This will return a text string in the format "YYYY-MM" which could then be stored in a text field.

2. SELECT CDATE(LEFT("200101",4) &"-" & RIGHT("200101",2)) AS YOURDATE
FROM your_table;
--This will convert the value to an actual date value (will equal "01/01/2000&quot which can be stored in a date field.

 

[Freckles]

Used method 2 and it worked beautifully!! Many Thanks

Deb Koplen
deb.koplen@telops.gte.com

A person can stand almost anything except a succession of ordinary days.

 

[MichaelRed]

UnixDate = 200101

MyDate = Format(Left(UnixDate, 4) & "/" & Right(UnixDate, 2), "yyyy" & "-" & "mm&quot
? MyDate
2001-01

This way doesn't add / force the extra value (Day-date as the first of the month) into your expression. Otherwise not really any different.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over