Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Unanswered: how to set datetime format ?

    sql server 2005

    default format datetime: mm/dd/yyyy hh:mm:ss
    I want to set format: dd/mm/yyyy hh:mm:ss

    help me. Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    set dateformat mdy
    select convert(datetime,'01/02/2009')
    set dateformat dmy
    select convert(datetime,'01/02/2009')

    -----------------------
    2009-01-02 00:00:00.000

    (1 row(s) affected)


    -----------------------
    2009-02-01 00:00:00.000

    (1 row(s) affected)

  3. #3
    Join Date
    Mar 2009
    Posts
    10
    Sorry, I can't use that.
    Let me explain it clearly

    My Table have 2 columns Id, myDate
    1 | 03/22/2009
    2 | 01/22/2009
    3 | 02/22/2009
    4 | 03/22/2010

    I want to create a query with result

    1 | 22/03/2009
    2 | 22/01/2009
    3 | 22/02/2009
    4 | 22/03/2010

    thanks for your reply

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT CONVERT(VARCHAR,MyDate,103) AS result_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    SELECT [id], 
       , CONVERT(VARCHAR(10), myDate, 103) + ' ' +
       CONVERT(VARCHAR(12), myDate, 114) AS [DD/MM/YYYY HH:MI:SS]
    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

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Here's a function for you:

    /****** Object: UserDefinedFunction [dbo].[fn_FormatDateTime] Script Date: 03/27/2009 09:48:55 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

    --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



  7. #7
    Join Date
    Mar 2009
    Posts
    10
    myDate column's type is Datetime, not varchar
    I can't convert to dd/mm/yyyy

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SQL Server has no control over how dates are displayed on your screen so this isn't a SQL Server question. You must configure your client application or presentation tier to display dates in the format you want. For example, most Windows applications use the format set in Windows Control Panel.

  9. #9
    Join Date
    Mar 2009
    Posts
    10
    thanks. ok
    I guest I must Convert int to string for display.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by trieuanhl
    thanks. ok
    I guest I must Convert int to string for display.
    Why would you do that? As I already said, you just need configure the format in the right place and you'll be fine. We can't tell you how to do it because you didn't tell us what client application or development language you are using, but there's certain to be a straightforward way to change the date format.

Posting Permissions

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