Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2002
    Posts
    36

    Unanswered: Timestamp Question

    Hello all,

    I have a column that is a datetime format with a date and time stamp, and I need to extract the time by itself (as a varchar) to insert into a new table.

    I've tried several different things, but can't seem to come up with the best way to do it.

    Can anyone assist?
    Thanks in advance,
    Dirk

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i dunno about the best way...

    try CONVERT(varchar(8),yourtimestamp,114)

    see
    http://msdn.microsoft.com/library/en...ca-co_2f3o.asp

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jun 2002
    Posts
    36
    No, unfortunately that does not work. It's a date/time stamp field. So the value in the field (for instance) is "6/17/2002 12:34:14 PM"

    I need to be able to insert the 6/17/2002 date into a datetime field (no problem there), but also convert the 12:34 PM time to a varchar and insert it into a second column.

    Dirk

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    how about

    Code:
    --------------------------------------------------------------------------------------------------
    declare @d datetime
    set @d = getdate()
    select @d as 'Date & Time', convert(varchar(10),@d,101) as 'Date', right(convert(varchar(25),@d,100),7) as 'Time'
    --------------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jun 2002
    Posts
    36
    Paul,

    Thanks for the reply. This is closer, but still not quite right. This pulls the second character of minutes and the seconds. What I really need to be able to do when all is said and done is convert the time into military time without the colon separators.

    So where the time segment of the datetime stamp displays 1:30:40 PM I need to be able to extract it as 1330 to insert into the new field. I can probably figure out a way to get there using temporary tables but I'd prefer to be able to do it in a single select somehow...

    Any other suggestions? I'm dealing with a very troublesome application front end, unfortunately!

    Thanks,
    Dirk

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which of the styles in the CONVERT function did you try?

    if 114 isn't right, pick another and pull out substrings

    rudy

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    As rudy said, you need to play with the diffrent convert options and find the one that gets you closest to what you want. Once you are close you can use the RIGHT, LEFT or SUBSTRING functions to do the rest.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Here is a small script that will display all of the formats for you, when using CONVERT

    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
    Output
    Code:
    Code|                                    Code|
    0   | Jun 27 2002  9:51AM                100 | Jun 27 2002  9:51AM                
    1   | 06/27/02                           101 | 06/27/2002                         
    2   | 02.06.27                           102 | 2002.06.27                         
    3   | 27/06/02                           103 | 27/06/2002                         
    4   | 27.06.02                           104 | 27.06.2002                         
    5   | 27-06-02                           105 | 27-06-2002                         
    6   | 27 Jun 02                          106 | 27 Jun 2002                        
    7   | Jun 27, 02                         107 | Jun 27, 2002                       
    8   | 09:51:03                           108 | 09:51:03                           
    9   | Jun 27 2002  9:51:03:733AM         109 | Jun 27 2002  9:51:03:733AM         
    10  | 06-27-02                           110 | 06-27-2002                         
    11  | 02/06/27                           111 | 2002/06/27                         
    12  | 020627                             112 | 20020627                           
    13  | 27 Jun 2002 09:51:03:733           113 | 27 Jun 2002 09:51:03:733           
    14  | 09:51:03:733                       114 | 09:51:03:733                       
    20  | 2002-06-27 09:51:03                120 | 2002-06-27 09:51:03                
    21  | 2002-06-27 09:51:03.733            121 | 2002-06-27 09:51:03.733
    MCDBA

  9. #9
    Join Date
    Jun 2002
    Posts
    36
    This is close, but still not what I need because of the military time issue. The problem is the time field in the app is a varchar field, so any PM times need to convert to military in order to display properly.

    All the code supplied was helpful, but it looks like I will need to combine these with an intermediate step in a temp table.

    Thanks to all for the assistance!
    Dirk

  10. #10
    Join Date
    Jun 2002
    Posts
    36
    This is close, but still not what I need because of the military time issue. The problem is the time field in the app is a varchar field, so any PM times need to convert to military in order to display properly.

    All the code supplied was helpful, but it looks like I will need to combine these with an intermediate step in a temp table.

    Thanks to all for the assistance!
    Dirk

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Code:
    --------------------------------------------------------------------------------------------------
    declare @d datetime
    set @d = getdate()
    select @d as 'Date & Time'
    , convert(varchar(10),@d,101) as 'Date'
    , right(convert(varchar(25),@d,100),7) as 'Time'
    , left(convert(varchar(25),@d,14),2) + substring(convert(varchar(25),@d,14),4,2) as 'Military Time'
    set @d = dateadd(hh,12,@d)
    select @d as 'Date & Time'
    , convert(varchar(10),@d,101) as 'Date'
    , right(convert(varchar(25),@d,100),7) as 'Time'
    , left(convert(varchar(25),@d,14),2) + substring(convert(varchar(25),@d,14),4,2) as 'Military Time'
    --------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Jun 2002
    Posts
    36


    OK, this works when I test it, but how do I apply it to my current table? I tried replacing the getdate() with the column name from my table, but my resulting fields all come out null??

    My originating field is called orderdate. I need to extract the time piece of this field out and convert it to a varchar, reflected in military time.

    Why does this work on getdate, but not on a specific column?
    Dirk

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT TOP 5
    coalesce(orderdate,'nada')
    , convert(varchar(25),orderdate,100)
    , left(convert(varchar(25),orderdate,14),2)
    + substring(convert(varchar(25),orderdate,14),4,2)
    FROM yourtable

    and let's see what's in there

    rudy
    p.s. for this type of problem, you really don't need a temp table -- i wanted to mention that earlier but missed my chance

  14. #14
    Join Date
    Jun 2002
    Posts
    36
    Here is the result set from the code:

    6/17/2002 12:45:00 PM 6/17/2002 12:45:00 PM 6/7/
    6/17/2002 7:14:00 AM 6/17/2002 7:14:00 AM 6/7/
    6/17/2002 8:07:00 AM 6/17/2002 8:07:00 AM 6/7/
    6/17/2002 8:07:00 AM 6/17/2002 8:07:00 AM 6/7/
    6/17/2002 10:13:00 AM 6/17/2002 10:13:00 AM 6/7/

    I'm stumped.
    Dirk

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's weird

    i'm stumped now too


Posting Permissions

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