need a little help with my convert statement of DATETIME TO DATE

Question

Post reply

need a little help with my convert statement of DATETIME TO DATE

Leeland

SSCrazy Eights

Points: 8959
More actions
November 18, 2004 at 8:01 am

#59802

Hello,
I have made a view which I am selecting records from 9 tables, one of which is a DATETIME field. In the crystal reports report that I am making I don't need to see the time so I have tried to covert the field in my sql select statement with out any luck. I am using crystal 9.0 which doesn't allow me to change the data type within crystal...at least I haven't figured out a good way to do it
here is what I have
select
...
...
...
convert(date,booking.book_date, 101)
** this is where i need help **
I want the format to be MM/DD/YYYY
From
table 1
table 2
I know this isnt' right but if someone could give me a little help I would appreciate it...I really can't find anything specific on google that has helped me so far
Thanks in advance
Leeland

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 1

Hi Lee,

Try this

select convert(char(10), booking.book_date,101) from TableName

Have Fun

Steve

We need men who can dream of things that never were.

Leeland SSCrazy Eights Points: 8959 More actions · Answer 2

Is there a way to get it to stay as a date or datetime data type ?

The reason I ask is because I need that field in a crystal report which will be used a date range parameter.

In the older versions of crystal reports they allowed for you to take a datetime field in crystal and convert it to just date...so you could use it as a parameter for date range.

In crystal 9.0 it doesn't have that option anymore...so when you use the parameter function of crystal you have to enter the date and also the time which would be an inconvience to my users using this report.

Thanks for the reply,

Lee

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 3

Hi Lee,

Not really spent much time with Crystal 9. Can you not allow the users to enter a normal dd/mm/yyy format as a string variable and convert it silently behind the scenes, adding the hh/mm/ss before you do any processing against it?

There are a couple of other threads you should have a look at:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=96557

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=2645

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=130933

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=15607

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=96063

Or

If you head for the main page here http://www.sqlservercentral.com/

and do a search on 'format date' or 'convert date' there are loads of threads on this problem.

Have fun

Steve

We need men who can dream of things that never were.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Lee,

To answer your question "Is there any way to keep it as a date or datetime value?" - No. In SQL Server there isn't a DATE datatype only DATETIME and that requires both a date and time. If one is missing SQL Server will add the default.

Now you could store the DATE with the default time, which is 00:00:00.000. Then every datetime will have the same time.

That might do what you need.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Another solution and one that I use:

Create a stored procedure on SQL Server that does what you want. Make sure it has the variables you want entered via Crystal Reports. Then provide the user access to the stored procedure (the same way you give them access to a table).

Then make a report that shows what you want and provide it to the users. When they run the report it will ask for the parameters.

-SQLBill

Leeland SSCrazy Eights Points: 8959 More actions · Answer 6

SQLBill,

Thank you for the replies...I did get it to work finally within crystal. I finally found the option that allows you to convert datetime fields to just date.

Me being new to both sql and crystal I didn't realize that it was just datetime and that you couldn't store it as date

it makes total sense to me now !!! Maybe when I get a little time under my belt I can tackle stored procedures

Thank you to all of you who offered advice I do appreciate it

Leeland