DateValue function

[TarunAgarwal]

Hi,

I have a column by the name of Request Date in my table, which is of type String. The date value (with time stamp) is being stored in this column with the format as string...

Now when I group by request date, using the datevalue function, the time stamp is being ignored, since the datevalue function only returns the date part of the string.

The time stamp is critical for me and I require it. Is there any function, which takes the time stamp also? I cant change the Request Date data type to Date. It has to be of String Type only.

Please help!

Regards,
Tarun

 

[JimHorton]

Tarun,
Cdate() will convert to date.
--Jim

 

[JimHorton]

Tarun,
Cdate() will convert to date/Time.
--Jim

 

[raskew]

Whoever decided to store Request Date as a string did you no favor.

Take a look at

http://support.microsoft.com/support/kb/articles/Q130/5/14.ASP

to see a discussion of how date/times data types are stored, i.e. as double-precision, floating-point numbers, with the integer portion of the double-precision number representing the date and the decimal portion representing the time.

Example, from the debug window:

x = now()
? x
11/24/01 7:26:31 AM
? cdbl(x)
37219.3100810185

The DateValue() function is of no use in your dilemma, since it considers only dates-not times. (see Help File)

As a quick-fix, you could use the CDate() function to return Request Date in date/time data type format and then group on the converted date/time.

Example:

SELECT CDate([Request Date]) AS dteReqDate
FROM Table1
GROUP BY CDate([Request Date]);

In the long run, it would probably be to your benefit to add an additional field to your table, then run an update query using the CDate() function to convert your string Request Date to proper date/type data type.

 

[TarunAgarwal]

Thanks Jim and Thanks raskew.
The Cdate() function worked fine!

Regards,
Tarun