Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    7

    Cool Unanswered: time date conversion

    SELECT @time = TO_CHAR(@theDate, 'MM/DD/YYYY HH:MMS')
    This is a conversion statement that we use currently in Oracle. I need to do this same function in Sql server. I have seen the options of cast and convert but they dont have anything is this exact format. Thanks again for all help.


  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    SELECT @time = convert(char(11),@theDate, 101) + convert(char(8),@theDate, 108)

    Usually it is better to let the presentation layer cope with the date formatting.

  3. #3
    Join Date
    Feb 2002
    Location
    Bucharest, Romania
    Posts
    1

    Lightbulb

    You may also like to try
    select cast(getdate() as varchar(19)).

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Here is a little piece of code that I use to remember the formats and how to put them together as nigelrivett did to get meet your needs

    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
    GO

    Output


    Code:
    Code|                                    Code|
    0   | Feb 28 2002  9:18AM                100 | Feb 28 2002  9:18AM                
    1   | 02/28/02                           101 | 02/28/2002                         
    2   | 02.02.28                           102 | 2002.02.28                         
    3   | 28/02/02                           103 | 28/02/2002                         
    4   | 28.02.02                           104 | 28.02.2002                         
    5   | 28-02-02                           105 | 28-02-2002                         
    6   | 28 Feb 02                          106 | 28 Feb 2002                        
    7   | Feb 28, 02                         107 | Feb 28, 2002                       
    8   | 09:18:04                           108 | 09:18:04                           
    9   | Feb 28 2002  9:18:04:437AM         109 | Feb 28 2002  9:18:04:437AM         
    10  | 02-28-02                           110 | 02-28-2002                         
    11  | 02/02/28                           111 | 2002/02/28                         
    12  | 020228                             112 | 20020228                           
    13  | 28 Feb 2002 09:18:04:437           113 | 28 Feb 2002 09:18:04:437           
    14  | 09:18:04:437                       114 | 09:18:04:437                       
    20  | 2002-02-28 09:18:04                120 | 2002-02-28 09:18:04                
    21  | 2002-02-28 09:18:04.437            121 | 2002-02-28 09:18:04.437

Posting Permissions

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