Formatting a DateTime variable

Results 1 to 3 of 3

Thread: Formatting a DateTime variable

  1. #1
    Join Date
    Dec 1969

    Default Formatting a DateTime variable

    I have a question about formatting a datetime variable. I basically have a datetime variable stored as dd/mm/yyyy. For instance, I declare a variable as datetime and it would be something like 1/1/2002: 12:00AM<BR><BR>I need to convert this by using the Cast function a format as <BR>January 1, 2002. <BR><BR>Can someone tell me how one would do this in T-SQL.<BR><BR>Thanks<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Formatting a DateTime variable

    there&#039;s not a function in T-SQL to handle this so you&#039;d have to write your own (if you have SQL Server 2000)<BR><BR>CREATE FUNCTION fnFormatDate<BR>( @theDate datetime)<BR><BR>RETURNS varchar(20)<BR>AS<BR>BEGIN<BR><BR>RETURN MONTHNAME(@theDate) + &#039; &#039; + DAY(@theDate) + &#039;, &#039; + YEAR(@theDate)<BR><BR>this returns Jan for January. If you need the full name, insert all the names in a table with an autoincrement id and use that table in a join on the monthnumber.<BR>

  3. #3
    Join Date
    Dec 1969

    Default To get January instead of Jan...

    According to the TSQL docs, you can do this:<BR><BR>RETURN [hl="yellow"]DATENAME(month,@theDate)[/hl] + &#039; &#039; + DAY(@theDate) + &#039;, &#039; + YEAR(@theDate)<BR><BR>instead of<BR><BR>RETURN MONTHNAME(@theDate) + &#039; &#039; + DAY(@theDate) + &#039;, &#039; + YEAR(@theDate)<BR><BR>(I couldn&#039;t find MONTHNAME in the online TSQL docs, actually. Dutch: Do you have a URL for it?)<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts