Converting date to char

[summitx]

I am having a bit of difficulty converting a date field into a char (8) within a access project. I can make the conversion work and the field populates within the table, however in format mmddyyyy if the month is January thru Sep the record will drop the first zero in the string.


I use somthing like.
dim sdate as string
sdate = format(me.datex,"mmddyyyy")

'this returns a correct value i.e. 01012006 but
when I insert into the table I am left with 1012006. This leads me to think that access has converted the date into an integer.
I have formated the table to recieve char(8). I am not going to bother posting my code because it inserts fine, I am just having a datatype issues I think.

Thanks

 

[cmmrfrds]

Dates (data type) are indeed stored as numbers and that is true in other databases as well. Each database has a little different algorithm to store the number.

Access uses 12/31/1899 as the starting point.
SQL server uses 01/01/1900 as the staring point.

Try this.
Debug.Print Now() - (#12/31/1899#)
You will get the days since that point in time.
38736

The fractional part of the number is the time of day of 24 hours.
38736.125
The .125 is 3 AM or 3 hours
.250 is 6 AM or 6 hours etc......

Because the date is stored as a number it is easy to do arithmetic on the date.

 

[summitx]

Thanks for the lesson, however my question revoles around converting the string mmddyyyy in a char as is requested by a client. This is a contraint of their parent database.

I Dont know why they want this but all the same, without doing an update on the record after it is inserted is there a way to enure that the record for feb 1 2006 will read 02012006 instead if what I presently have as 2012006 notice the dropped zero at the start of the string.

thanks,

 

[cmmrfrds]

You are correct in that moving/casting the character string 02012006 to a numeric field will result in 2012006 when displayed from the numeric field without formatting. If the client is storing in a numeric field then it would need to be reinterpreted when displaying to add back the leading zero.

 

[danvlas]

Use this expression in your SQL view/function/stored proc:

Cast(Month(YourDateField) As nvarchar(2)) + Cast(Day(YourDate) As nvarchar(2)) + Cast(Year(YourDateField) As nvarchar(4))

This will return a nvarchar(8).

However, the notice about the leading zero in a numeric field (as cmmrfrds has pointed out) stands.

Is it about STORING or DISPLAYING the information?

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[summitx]

Thanks for all of your replies. The consensus is to keep the date as is in the adp file ie. 38740 to easily perform all date calulations and necessary sprocs. When needed, the app will do literal translations ( split the date into MM dd yyyy)and recombine as a string in the procedures (or possibly vba) that require information when linking to a date in the old form ,(mmddyyyy) this should work well when no date calculations are required.

Thanks,

J