cast date time to string

Thanks again,
I used type 102 instead of 120
One I use 120 then i have date separator correct,

Thanks again,


On May 5, 4:55 pm, iccsi wrote:
> On May 5, 4:05 pm, "Bob Barrows" wrote:
>
>
>
>
>
> > iccsi wrote:
> > > I have following code to cast datepart to a char, but I get
> > > 'Incorrect syntax near 'cast', expected 'AS'.'
>
> > The error message is pretty explicit ....
>
> > This
> > > cast(datepart(yyyy,weekending),char(4)) + '-' +
>
> > should be
> > cast(datepart(yyyy,weekending) AS char(4))
>
> > >    cast(datepart(m, weekending),char(2)) + '-' + cast(datepart(d,
> > > weekending),char(2)) as weekending from #w
>
> > You're going to too much trouble. The CONVERT function has a style argument
> > that lets you specify this particular format:
>
> > select CONVERT(varchar(10),weekending,120) as weekending from #w
>
> > The 120 specifies yyyy-mm-dd hh:mi:ss(24h)
> > The varchar(10) uses just the first 10 characters: yyyy-mm-dd
>
> Thanks for helping,
> My date separator is ".", is it possible to change to "-"
>
> Thanks again,- Hide quoted text -
>
> - Show quoted text -
 >> Stay informed about: cast date time to string 

iccsi wrote:
> I have following code to cast datepart to a char, but I get
> 'Incorrect syntax near 'cast', expected 'AS'.'

The error message is pretty explicit ....
>
This
> cast(datepart(yyyy,weekending),char(4)) + '-' +
should be
cast(datepart(yyyy,weekending) AS char(4))


> cast(datepart(m, weekending),char(2)) + '-' + cast(datepart(d,
> weekending),char(2)) as weekending from #w
>

You're going to too much trouble. The CONVERT function has a style argument
that lets you specify this particular format:

select CONVERT(varchar(10),weekending,120) as weekending from #w


The 120 specifies yyyy-mm-dd hh:mi:ss(24h)
The varchar(10) uses just the first 10 characters: yyyy-mm-dd
 >> Stay informed about: cast date time to string 

iccsi ( ) writes:
> On May 5, 4:05 pm, "Bob Barrows" wrote:
>> iccsi wrote:
>> The 120 specifies yyyy-mm-dd hh:mi:ss(24h)
>> The varchar(10) uses just the first 10 characters: yyyy-mm-dd
>
> Thanks for helping,
> My date separator is ".", is it possible to change to "-"

Had you consulted the topic CAST and CONVERT in Books Online, you would
have seen that Bob slipped on the number. The format code you want is 121.


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 >> Stay informed about: cast date time to string