SQL DateSerial problems

  • Thread starter Thread starter daver676
  • Start date Start date
D

daver676

Hello All!

I am using SQL to format the dates in my MS Access tables
from YYMMDD to MMDDYYYY. However there is a problem. When
converting a date value of 880105, my DateSerial returns
09/05/154. Also, when converting 000000, DateSerial
returns 11/30/1999, when it should return 1/1/00, right?

Here is my SQL code:

UPDATE ICVEND SET ICVEND.LastDateRcvd = DateSerial(Left
([LastDateRcvd],2),Mid([LastDateRcv
d],2,3),Right([LastDateRcvd],2));

What is the problem?

Thanks

Dave
 
daver676 said:
Hello All!

I am using SQL to format the dates in my MS Access tables
from YYMMDD to MMDDYYYY. However there is a problem. When
converting a date value of 880105, my DateSerial returns
09/05/154. Also, when converting 000000, DateSerial
returns 11/30/1999, when it should return 1/1/00, right?

Here is my SQL code:

UPDATE ICVEND SET ICVEND.LastDateRcvd = DateSerial(Left
([LastDateRcvd],2),Mid([LastDateRcv
d],2,3),Right([LastDateRcvd],2));

What is the problem?
Click to expand...

DateSerial produces *Dates* and what you have stored are not dates, they are numbers
(or possibly text). If you were actually storing dates then formatting would not be
something you could change with an update query as date formatting affects display
only. It has nothing to do with the value that is stored which is exactly the same
regardless of what formatting you might apply.
 
But doesn't DateSerial also convert these numbers (or
text) to data type date? Why else would it be called
DateSerial? BTW, it formatted correctly, I just had the 2
and 3 in Mid backwards. It should have been:

UPDATE ICVEND SET ICVEND.LastDateRcvd = DateSerial(Left
([LastDateRcvd],2),Mid([LastDateRcvd],3,2),Right
([LastDateRcvd],2));
I would still love some clarification on my date question
though. Thanks.

Dave
-----Original Message-----
daver676 said:
Hello All!

I am using SQL to format the dates in my MS Access tables
from YYMMDD to MMDDYYYY. However there is a problem. When
converting a date value of 880105, my DateSerial returns
09/05/154. Also, when converting 000000, DateSerial
returns 11/30/1999, when it should return 1/1/00, right?

Here is my SQL code:

UPDATE ICVEND SET ICVEND.LastDateRcvd = DateSerial(Left
([LastDateRcvd],2),Mid([LastDateRcv
d],2,3),Right([LastDateRcvd],2));

What is the problem?
Click to expand...

DateSerial produces *Dates* and what you have stored are not dates, they are numbers
(or possibly text). If you were actually storing dates then formatting would not be
something you could change with an update query as date formatting affects display
only. It has nothing to do with the value that is stored which is exactly the same
regardless of what formatting you might apply.


.
Click to expand...
 
daver676 said:
But doesn't DateSerial also convert these numbers (or
text) to data type date? Why else would it be called
DateSerial? BTW, it formatted correctly, I just had the 2
and 3 in Mid backwards. It should have been:
Click to expand...

Yes it does, but if you take the date returned by DateSerial and stuff it into a
numeric field in a table, it will be converted to the number that Access actually
uses for Date storage. This number won't look anything like the date value. EX:
12/30/1899 = 0 when stored as a number.
 
Back
Top