Re: convert a date to a string?

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 14:17:30 -0700

Fred

The 3rd argument to convert is primarily used to format a datetime when
converting it to a CHARACTER string.
Look again to find the numeric code for the style you want, then convert to
a varchar...

SELECT CONVERT(varchar(30), '1/1/2004', style-number)

Also, don't convert it back to a datetime, or put it in a datetime variable,
or you'll lose the format. Datetime values cannot be formatted. Only when
they exist as a string... 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Fred" <anonymous@discussions.microsoft.com> wrote in message
news:1e4301c49b8f$07492800$a601280a@phx.gbl...
>I have already tried
>
> select Convert(datetime, '1/1/2004', 'dd-mm-yy')
>
> but got the error message that
> "Argument data type varchar is invalid for argument 3 of
> convert function", if I remove the single quotes I
> get "Invalid column name"
>
> How do I enter dd-mm-yy
>
>
>>-----Original Message-----
>>Yes, read the docs in Books Online for CONVERT and check
> out the 3rd
>>parameter.
>>
>>-- 
>>HTH
>>----------------
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>
>>"Fred" <anonymous@discussions.microsoft.com> wrote in
> message
>>news:305b01c49b79$56d0f760$a501280a@phx.gbl...
>>> Yes,  I want to select a date column from a table and
> view
>>> it as a string.  Example  '1/1/2004'  would
> be 'January-
>>> 04', '2/15/2004' would be 'February-2004'.  I was
> going to
>>> write a UDF where I manually pick the Month and Year
> of my
>>> date and say
>>>
>>> Declare @m int
>>> Declare @y int
>>> Declare @mon varchar(20)
>>> Declare @yr varchar(2)
>>> Declare @result varchar(22)
>>>
>>> Set @m = Month(@myDate)
>>> Set @y = Year(@myDate)
>>> If @m = 1
>>>  Set @mon = 'January'
>>> ...
>>> Set @result = @mon + '-' + @yr
>>> Return @result
>>>
>>> Is there a better way to do this?  Like in MS Access
> they
>>> have the Format Function.  Is it possible to use
> Convert
>>> to do something like this?
>>>
>>> TIA
>>> Fred
>>
>>
>>.
>>

Relevant Pages

  • Re: tab in userform locks program
    ... > I am a VBA newbie and have not been using DIM in my code so it is ... declare a variable at the top of a standard module using Private (module ... You may notice that I used Format$ instead of Format. ... but Format$ returns a String whereas ...
    (microsoft.public.excel.programming)
  • Re: dateTime Manupulations
    ... Notice the quoted string, a specific format for a date & time. ... will need to convert that string into a DateTime. ... > datetime conversion between strings, however it was a datetime conversion ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Date Problems - ASP/SQL
    ... a DATETIME column does NOT store DD/MM/YYYY format. ... DECLARE @dt SMALLDATETIME ... data> type to a datetime data type resulted in an out-of-range datetime value. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Axis <-> .Net xsd:date interoperability problem
    ... .NET can de-serialize from alternative string representations of the date, ... but you need to tell it the format to expect. ... DateTime to be set. ... serialization of all DateTime's. ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: Search an Outlook appointment
    ... Then I can make my search string using the strDate. ... And my problem is to convert datetime to a string: ... How can I get the right datetime format of the user for making the search? ... I need to search for Outlook appointements from date to date. ...
    (microsoft.public.win32.programmer.messaging)