Convert string field to date field

[Hillary]

In Crystal SQL Designer I created a table. (This is my first SQL Table) When I bring the table over to Crystal Reports it converts the 2 DateTime fields into 2 different String fields. One is (YYYY,MM,DD HH:MM:SS) and the other is the same except there is a period at the end of the SS. I need to convert the data into a Date/DateTime filed so I can put a Date Range parameter around it.

How do I convert String type data into Date or preferablly DateTime data?

Thanks for you help!

Hillary

 

[synapsevampire]

I don't use the Crystal SQL designer, and never suggest doing so, it's an additional layer that is basically an inefficient means of SQL programming, try to use Views or SP's, and rumor has it that this functionality may go away...

You can use the datetime function to convert a string to a datetime type, and use the date function to convert to just a date.

If you find that it cannot convert it, it's probably because your datetime string is not formatted correctly, so reconstruct it accordingly.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Hillary]

I originally had this report in Crystal, but because of the way the tables are set up in the database I can not get Crystal to limit the records the way I need them. For some reason Crystal SQL Designer will. When I created the original report in Crystal Reports the dates were fine. I figure either Crystal SQL Designer is bringing over the dates differently or somehow in the transition the date type is changing.

Any other ideas??? What are the Views and SP's you are referring too?

Thank you for you time.

Hillary

 

[synapsevampire]

The quick fix is to use the datetime() function against these fields, though the one with the extra period on it will have to be truncated.

A View is a means for linking tables into a single table View in most databases, and an SP is a Stored Procedure, which is a set of SQL statements which return a single recodset (when using Crystal), SP's having an execution plan so they're faster, and they allow more functionality. This generally requires special permissions in the database.

If you shared with us the type of database that you're using, and the intent (show examples), there are many experts here that might show you how to accomplish the goal without using SQL Designer.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com