[RESOLVED] DATE issue....

language: VB6
database: MS ACCESS 2000
AMERICAN short date format: M/d/yyyy
INDIAN short date format: d/M/yyyy........(intended format)


i have an mdb table with 2 fields: SignIn DATE & TIME in DATE/TIME format

now in vb, u must be knowing that its stored in the US format by default..the date
and i want to convert it into INDIAN format so i am uing the FORMAT( ) ..ok ?
convertions is happenniing fine

so i am using to Date datatype variables : dteID & dteIT for storing the signin date & time from the db...ok
i am also wanting to store the current date & time in two other date datatype variables: dteOD & dteID

i need two different dates and times..
becoz i want to find the difference between them by using the concatenated expression of dteID & dteID and dteOD & dteOD....

and then i am storeing the concatenated value of dteID & dteIT in dteEntryChrono
and then i am storeing the concatenated value of dteOD & dteOT in dtExitChrono

ISSUE:
----------------------------------------------------------------
values in debug mode:
----------------------------------------------------------------

dteID = 10/11/2006

dteIT = 6:03:51 PM

concatenation code line used: dteID & " " & dteIT

dteEntryChrono = dteID & " " & dteIT
|
|_______10/11/2006 6:03:51 PM



dteOD = 17/11/2006

dteOT = 5:24:24 pm

concatenation code line used: dteOD & " " & dteOT

dteExitChrono = dteOD & " " & dteOT
|
|_______11/17/2006 5:24:24 pm.......HOW CHANGE IN FORMAT

----------------------------------------------------------------

how is it possible that "dteExitChrono" is showing 11/17/2006....when the variables dteOD is showing 17/11/2006...????

--
Regards,
Gautam

That will only happen if your using strings somewhere... there's a difference between dates in a string and dates in a true datetime data type, which is numeric (whole numbers represent days and decimal digits represent time) but represented/formatted/shown to the user in the familiar string like format. With true datetime data type, being numeric, x days is x days whatever formatting or regional setting you use... on the other hand a date in a string is "interpreted" on the fly by the date functions hence the possibility of swapping months and days during conversion when regional settings change.

With that said I doubt your really using datetime data type all throughout, and resorted to strings somewhere. So what's the data type of the field your using in the database?

BTW, welcome to the forums

Welcome to the forums. :wave:

I have edited your post and removed your email address.

You should never post your email address in an open post on an open forum. Mail spam bots can pick that up and before you know it, your mailbox is full of junk mail. If you wish to share your email address with other forum members, please do so via our PM system.

In addition, we prefer all answers to questions be publically posted rather than sent via EMail or PM. That way, everyone with a similar problem can benefit.

Thanks. :)

leinad31, thanks for the prompt reply. But pls note that i have not used a string variable for this code at all.

The database field is: Signin Date ..having the DATE/TIME format in ms access design view. I have the mini proj ready. If you arrange a chat live session via voice if possible, then i could send u my file and u could check it out too...in debug mode and see what to do.

--
Regards,
Gautam

format returns a string, if you put that into a date variable, it will likely change it back to your local setting, and may behave differently for dates it can tell are wrong, that is with a day value greater than 12

Edit: here is an example

VB Code:

Dim d As Date, d1 As Date, mydate As String
 d = "2/3/99"   ' 2nd march
 d1 = "23/5/06"
mydate = Format(d, "mm/dd/yy")  'returns 03/02/99
d = mydate  'returns  3/02/1999
mydate = Format(d1, "mm/dd/yy")  'returns  05/23/06
d1 = mydate 'returns  23/05/2006

Your doing concatenation which returns a string... if you try to extract dates from that string then you will have problems. And you really didn't need two separate variables to hold date and time respectively... but since you already did it that way, to combine the date and the time try adding the datetime variables dteID + dteIT.

And in your previous sample that days/months got switched for 10/11/2006 6:03:51 PM you just didn't realize it cause your interpreting it with the wrong format... try using three spaces for month (eg. Jan, Feb, Mar...) on the two dates and you'll see what I mean.

To get around that default formatting (according to system settings) applied to strings use format function as suggested above.

And lastly, there was no need to send a PM along with posting in this thread. Posting in the thread is sufficient.

Hi,

i saw ur reply.
OK
Your also showing me a code block that is similar to mine. But where is the solution to this issue.

I also tried this using the plus + symbol for concatenation of the two date variables: dteOD + dteOT ....this gave me a data mismatch error.

Please provide a way to overcome this issue....

--
Regards,
Gautam

you can use Now to get both Date and Time, then format it to return a string that can be used in your display or query

VB Code:

dtechrono = Format(Now, "mm/dd/yyyy h:mm:ss")

if dtechrone is declared as a date datatype then it will reconvert the format to somesort of local setting format, so dtechrono must be a string, as you didn't show what dtechronos declared as, could even be textboxes for all i know, but from you problem looks like they must be date types

Quote:

---

Originally Posted by intellogo

Hi,

i saw ur reply.
OK
Your also showing me a code block that is similar to mine. But where is the solution to this issue.

I also tried this using the plus + symbol for concatenation of the two date variables: dteOD + dteOT ....this gave me a data mismatch error.

Please provide a way to overcome this issue....

--
Regards,
Gautam

---

If + throws an error then your obviously[ not using numeric datetime data types, so your statement that you are using the correct data type is false... which brings us back to the initial post about strings.

hey buddy, could we meet on YAHOO IM, so that i can talk to u LIVE! and solve this and another issue, online. I am actually unable to solve this code format issue. ??

Regards,
Gautam

The solution is pretty simple... use the date data type, don't use strings. When building dates, again don't use strings (.Text property, return value of Format(), etc) either refer to the numeric value (think of it as a double) or build the date with DateSerial or TimeSerial.

VB Code:

Private Sub Form_Load()
Dim myDate As Date
Dim myTime As Date
 
   myDate = DateSerial(2006, 10, 11)
   myTime = TimeSerial(6 + 12, 3, 51)  '6+12 for 6PM
   Debug.Print myDate + myTime  'display date and time info as date literal
 
   Debug.Print CDbl(myDate)  'display as double, whole number since no time info in myDate
   Debug.Print CDbl(myTime)  'display as double, decimal value between 0 and 1 represents time
   Debug.Print CDbl(myDate + myTime)
End Sub

I converted to double so VB doesn't autoformat the numeric as a date literal in the immediate window... so you can see and understand what I meant about dates being numeric. eg, Now + 1 means add one day to current date.

Another problem with the date functions, when string dates are passed to them, is that they TRY to return a valid date instead of throwing an error when string date doesn't comply with format specified in settings, as the following immediate window test shows.

Code:

---

debug.Print cdate("13/1/2006")  'this should have been an erroneous date
1/13/2006  'date above reinterpreted as this date
debug.Print CDate("1/13/2006")
1/13/2006

---

So you see, you really should avoid as much as possible string dates... you will have to update your source code where applicable to avoid string date pitfalls; storing/retrieving dates to/from the database, processing date inputs/outputs/intermediates, etc

hi buddy,

thanks for the details reply....but

let me inform u that i have not used any string variables nor "string date expresions" to represent dates. But i am still having this issue..

I would prefer chatting with u on YAHOO! ...clarify this issue...when i send my code to u ...as a mini project...

--
Regards,
Gautam

Quote:

---

debug.Print cdate("13/1/2006") 'this should have been an erroneous date
1/13/2006 'date above reinterpreted as this date
debug.Print CDate("1/13/2006")
1/13/2006

---

note that he is mot using US date format (as you are), but D/mm/yyyy
access database return date fields in US date format, which does not match his local date format