Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Format Date Column to MMDDYYYY Format

    Can someone please help me figure out the proper syntax to format the date column in my query below in the following format MMDDYYYY. I don't want to update my date field I just want to format the output.

    I tried the following but can't quite get it to work

    Select a,b,c,d, (SELECT REPLACE(CONVERT (VARCHAR(10),[Completed Date], 101), /, ) AS MMDDYYYY from mytable)
    from mytable


    Thanks,

    DB

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why the HECK do you have a nested subquery in there?
    And why the HECK are you formatting data in your database? That is what reporting tools and application interfaces are for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dsmbwoy
    I tried the following but can't quite get it to work
    whenever you have a problem, it is strongly advised that you be just a wee bit more specific than "can't quite get it to work"

    table layouts, sample rows, expected results -- these are the sine qua non of question formulation, but even more important, when a problem occurs, it is vital that you tell us the actual error message

    a nested subquery such as the one you have will work only if the table contains no more than one row, and in this case i'm going to guess that your table has more than one row

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2005
    Posts
    91
    I was hoping that someone could guide me in the right direction. My example above was just a try an attempt that does not work. I know that I can format dates through the use of reporting tools such as Crystal, excel etc. In this particular situation however I need to export a table as a flat file with the date formatted as MMDDYYYY versus the format that the field is currently in '1/21/2009 7:52:38 AM'. Is what I am trying to attempt not possible?


    Thanks,

    DB

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of course it's possible

    just ditch the subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2008
    Posts
    135
    select a,b,c,d,replace(convert(varchar(10),getdate(),101) ,'/','')asMMDDYYY from urtable
    see this link for dateformats
    SQL Server Helper - Tips and Tricks - Date Formats

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    blkr,

    Thanks a lot for the URL! OMG, this will simplify my life a lot
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dsmbwoy
    In this particular situation however I need to export a table as a flat file with the date formatted as MMDDYYYY versus the format that the field is currently in '1/21/2009 7:52:38 AM'. Is what I am trying to attempt not possible?
    OK. You are excused, then.
    Are you going to use an ETL tool such as DTS or SSIS to export the file? You may be able to specify the formatting at the time of export.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2005
    Posts
    91
    Thanks to everyone for the help. The syntax below is what I needed and used to convert my date field to the following format MMDDYYY.

    REPLACE(CONVERT (VARCHAR(10), dbo.table.field, 101), '/', '')

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    here's a function for ya.

    DATE: 4/13/2007
    PURPOSE: Manipulate date strings into desired formats.

    EXECUTION: fn_FormatDateTime '20070515', 'MM/DD/YYY'
    */
    CREATE FUNCTION [dbo].[fn_FormatDateTime]
    (
    @dt DATETIME,
    @format VARCHAR(16)
    )
    RETURNS VARCHAR(64)
    AS
    BEGIN
    DECLARE @dtVC VARCHAR(64)
    SELECT @dtVC = CASE @format

    WHEN 'LONGDATE' THEN

    DATENAME(dw, @dt)
    + ',' + SPACE(1) + DATENAME(m, @dt)
    + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
    + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

    WHEN 'LONGDATEANDTIME' THEN

    DATENAME(dw, @dt)
    + ',' + SPACE(1) + DATENAME(m, @dt)
    + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
    + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
    + SPACE(1) + RIGHT(CONVERT(CHAR(20),
    @dt - CONVERT(DATETIME, CONVERT(CHAR(8),
    @dt, 112)), 22), 11)

    WHEN 'SHORTDATE' THEN

    LEFT(CONVERT(CHAR(19), @dt, 0), 11)

    WHEN 'SHORTDATEANDTIME' THEN

    REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
    'AM', ' AM'), 'PM', ' PM')

    WHEN 'UNIXTIMESTAMP' THEN

    CAST(DATEDIFF(SECOND, '19700101', @dt)
    AS VARCHAR(64))

    WHEN 'YYYYMMDD' THEN

    CONVERT(CHAR(8), @dt, 112)

    WHEN 'YYYY-MM-DD' THEN

    CONVERT(CHAR(10), @dt, 23)

    WHEN 'YYMMDD' THEN

    CONVERT(VARCHAR(8), @dt, 12)

    WHEN 'YY-MM-DD' THEN

    STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),
    5, 0, '-'), 3, 0, '-')

    WHEN 'MMDDYY' THEN

    REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))

    WHEN 'MM-DD-YY' THEN

    CONVERT(CHAR(8), @dt, 10)

    WHEN 'MM/DD/YY' THEN

    CONVERT(CHAR(8), @dt, 1)

    WHEN 'MM/DD/YYYY' THEN

    CONVERT(CHAR(10), @dt, 101)

    WHEN 'DDMMYY' THEN

    REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))

    WHEN 'DD-MM-YY' THEN

    REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')

    WHEN 'DD/MM/YY' THEN

    CONVERT(CHAR(8), @dt, 3)

    WHEN 'DD/MM/YYYY' THEN

    CONVERT(CHAR(10), @dt, 103)

    WHEN 'HH:MMS 24' THEN

    CONVERT(CHAR(8), @dt, 8)

    WHEN 'HH:MM 24' THEN

    LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)

    WHEN 'HH:MMS 12' THEN

    LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))

    WHEN 'HH:MM 12' THEN

    LTRIM(SUBSTRING(CONVERT(
    VARCHAR(20), @dt, 22), 10, 5)
    + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))

    WHEN 'FILEDTSTAMP' THEN

    REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) +
    REPLACE(LEFT(CONVERT(VARCHAR(8), @dt, 8), 5), ':',SPACE(0))

    ELSE

    'Invalid format specified'

    END
    RETURN @dtVC
    END



Posting Permissions

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