Need a little help with T-SQL convert function

[leeland]

Hello,

I have made a view which I am selecting records from 9 tables, one of which is a DATETIME field. In the 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

here is what I have

select
...
...
...
convert(date,booking.book_date, 101)


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

 

[MrChad]

What format do you want?

MSDN link

You want:

CONVERT(varchar(20), booking.book_date, 101)

Change 101 to whatever date style you want.

 

[leeland]

Originally posted by: MrChad
What format do you want?

MSDN link

You want:

CONVERT(varchar(20), booking.book_date, 101)

Change 101 to whatever date style you want.

Thanks a lot, it worked like a charm...!!!

Leeland

 

[leeland]

One last question for you, why does it need to be varchar ? doesn't date as a data type work ? or does it need to be a varchar ?


thanks

leeland

 

[MrChad]

There's no such thing as a "date" data type in SQL Server. All dates are stored as datetimes. The CONVERT function transforms the datetime value (stored as a long in the database?) into a string (varchar) that can be formatted as you see fit.

 

[leeland]

Originally posted by: MrChad
There's no such thing as a "date" data type in SQL Server. All dates are stored as datetimes. The CONVERT function transforms the datetime value (stored as a long in the database?) into a string (varchar) that can be formatted as you see fit.

Well that makes sense...the reason I asked is because I was creating a crystal report and I wanted one of the parameters to be a date time frame and by using the varchar convert statement it makes it a string which I can't use.


Thanks I guess I will experiment alittle more


leeland

 

[DT4K]

Originally posted by: leeland

Originally posted by: MrChad
There's no such thing as a "date" data type in SQL Server. All dates are stored as datetimes. The CONVERT function transforms the datetime value (stored as a long in the database?) into a string (varchar) that can be formatted as you see fit.

Well that makes sense...the reason I asked is because I was creating a crystal report and I wanted one of the parameters to be a date time frame and by using the varchar convert statement it makes it a string which I can't use.


Thanks I guess I will experiment alittle more


leeland

You could leave it as a datetime coming back from sql and just format the display however you want in crystal.

Or if you just want to strip the time portion off because you need to use that value to represent that date in a query or something, convert it to a varchar format without time values, then convert or cast that varchar back to a datetime. You'll end up with a datetime value with 00:00:00 as the time.