Date formatting - Really newbie question

I have a unbelievably stupid problem that I can't figure out. My table is
imported from access and contains a column with data type datetime. I want
to be able to sum the data (which is a door counter for our store) to show
me all of the traffic for the day, then display the date as 1/1/2007 instead
of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
BOL help on this topic)...
convert(smalldatetime, [Date], 101)
No matter what I put in the style portion of the select statement it has no
impact on my output format. I suppose I could convert this to a varchar and
trim the results but that seems to be overkill (in addition to beiing a poor
solution).
Solved this. If anyone else is as inexperienced as I am and has this
problem, you can use the following method to change the output of the
datetime in this method.
Select CONVERT(varchar(20), [Date] as DT
will change this
1/30/2007 10:00:00
to this
1/30/2007
"Chuck G." wrote:

> I have a unbelievably stupid problem that I can't figure out. My table is
> imported from access and contains a column with data type datetime. I want
> to be able to sum the data (which is a door counter for our store) to show
> me all of the traffic for the day, then display the date as 1/1/2007 instead
> of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
> BOL help on this topic)...
> convert(smalldatetime, [Date], 101)
> No matter what I put in the style portion of the select statement it has no
> impact on my output format. I suppose I could convert this to a varchar and
> trim the results but that seems to be overkill (in addition to beiing a poor
> solution).
>
|||I don't quite see how that works (20 is too long, and the is only one
parenthesis). To
convert a datetime value to the same date at midnight, this is one solution:
dateadd(day,datediff(day,0,[Date]),0)
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Chuck G. wrote:
[vbcol=seagreen]
>Solved this. If anyone else is as inexperienced as I am and has this
>problem, you can use the following method to change the output of the
>datetime in this method.
>Select CONVERT(varchar(20), [Date] as DT
>will change this
>1/30/2007 10:00:00
>to this
>1/30/2007
>"Chuck G." wrote:
>

Date Formats/Styles

Hi, Is there any list available of style codes used in formatting dates
e.g Convert(Varchar(11),GetDate(),106)
I cant find these style codes or this method documented in BOL.
I want to format a date like this: 09 August 2005. How?
Thanks.
hals_leftBetter to do the formating in the client side.
CAST and CONVERT
http://msdn.microsoft.com/library/d...br />
2f3o.asp
AMB
"hals_left" wrote:

> Hi, Is there any list available of style codes used in formatting dates
> e.g Convert(Varchar(11),GetDate(),106)
> I cant find these style codes or this method documented in BOL.
> I want to format a date like this: 09 August 2005. How?
> Thanks.
> hals_left
>|||Did you look up CONVERT in BOL? But there is not a format like that
available. You could write a pretty easy function to return the data like
that using DATEPART and DATENAME, but unless you really have to use SQL, as
Alejandro said, use the client to change the appearance of the data.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123616217.059593.295580@.g44g2000cwa.googlegroups.com...
> Hi, Is there any list available of style codes used in formatting dates
> e.g Convert(Varchar(11),GetDate(),106)
> I cant find these style codes or this method documented in BOL.
> I want to format a date like this: 09 August 2005. How?
> Thanks.
> hals_left
>