Results 1 to 4 of 4

Thread: Date Format

  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Unanswered: Date Format

    Hi Everybody,

    I wanted to convert the date format from date time (2000-07-12 08:00:00.000 ) to only date (2000-07-12 ) in my SQL Query. I am using Microsoft SQL Server 2000. Any help is appreciated.

    Thanks
    Tarun

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    convert(char(10),yourdate,120)

    see CAST and CONVERT

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    r937 is correct in saying to use CONVERT(<datatype>,<field>,<code>).
    Here is a little script I keep around to help remind me of all of the date formats and their codes:
    Code:
    set nocount on
    
    declare	@dt	as datetime
    declare	@i	as tinyint
    
    set @dt = getdate()
    set @i	= 0
    
    print 'Code|' + space(36) + 'Code|'
    
    while @i < 22 begin
        print convert(char(3),@i) 
           + ' | ' + convert(char(35),@dt,@i) 
           + convert(char(3),(100+@i)) 
           + ' | ' + convert(char(35),@dt,(100+@i)) 
        set @i = @i+1
        if (@i = 15) set @i = 20
    end
    The output:
    Code:
    Code|                                    Code|
    0   | Sep 17 2002 10:00AM                100 | Sep 17 2002 10:00AM                
    1   | 09/17/02                           101 | 09/17/2002                         
    2   | 02.09.17                           102 | 2002.09.17                         
    3   | 17/09/02                           103 | 17/09/2002                         
    4   | 17.09.02                           104 | 17.09.2002                         
    5   | 17-09-02                           105 | 17-09-2002                         
    6   | 17 Sep 02                          106 | 17 Sep 2002                        
    7   | Sep 17, 02                         107 | Sep 17, 2002                       
    8   | 10:00:25                           108 | 10:00:25                           
    9   | Sep 17 2002 10:00:25:763AM         109 | Sep 17 2002 10:00:25:763AM         
    10  | 09-17-02                           110 | 09-17-2002                         
    11  | 02/09/17                           111 | 2002/09/17                         
    12  | 020917                             112 | 20020917                           
    13  | 17 Sep 2002 10:00:25:763           113 | 17 Sep 2002 10:00:25:763           
    14  | 10:00:25:763                       114 | 10:00:25:763                       
    20  | 2002-09-17 10:00:25                120 | 2002-09-17 10:00:25                
    21  | 2002-09-17 10:00:25.763            121 | 2002-09-17 10:00:25.763
    MCDBA

  4. #4
    Join Date
    Aug 2002
    Posts
    20
    Try this.

    Select convert(varchar(10),getdate(),120)

    Output
    ----------
    2002-09-17

    /Mada

Posting Permissions

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