Date Conversion

[chorsell]

I have a small issue with date conversion... One of the fields in my database(SQL 2000) holds a date and time (i.e 1900-01-01 11:55:00)
When I look at the field via crystal it sees the field as a Date field, When I look at the field via Access 2000 it correctly displays the time.

I have tried converting the fields to DateTime however the data returned is always 01/01/1900 12:00 AM.

Can anyone help? Ultimately I just want the time...

 

[Parkypark]

Not sure if this will help

There is a setting in the file menu of report options

in here you can set the format of date fields

to date or to datetime

It is probably woth checking this first

cheers
Parkypark

 

[mbarron]

Go to File / Options... as Parkypark suggests, but not to the Fields tab for formatting the field. Go to the Reporting tab instead. In the Date-time field option set to Keep Date-Time type?

If not...
What formulas have you tried?

Mike
If you're not part of the solution, you're part of the precipitate.

 

[chorsell]

Thanks for the tips but neither helped

Field is behaving like a date field in crystal - have converted to date time using
CDateTime ({Parking_Facts.Offence_Time})
However data shows as

1/01/1900 12:00:00AM

In Access data shows as Date/Time field Displayed as 11:11:00 AM

In database raw data 1900-01-01 12:24:00.000 and is a Date time field

 

[synapsevampire]

Converting to cdatetime won't help if it's a date type, it's already lost the time info.

Very surprising to hear that the "In the Date-time field option set to Keep Date-Time type" didn't resolve.

But here's a means to sanity check whether the data is being returned correctly:

Add a SQL Expression called strdate containing:

convert(varchar(23), table."datefield",20)

Add a formula containing:

cdatetime(%strdate)

Display what's in the formula.

If you're getting the time, then your settings are probably off within Crystal.

At any rate, this will give you a means to get the date and time into a datetime field.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com