Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Casting Varchar to Time in Sqlserver 2005

    Hi,

    I am having a Varchar column which is having value like '9:00 am'. Now i want to convert it to hh:mm:ss format while fetching from the table.

    I tried the following query

    Code:
    Select cast(colname as time(0)) as time from table
    But i am getting an error saying 'time' is not a proper data format.

    Can somebody guide me what i am doing wrong..

    Thanks for your help in advance,,
    Magesh

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    datetime
    not time

    Data Type**SQL Server / T-SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    Thanks mishaalsy,,
    But again, there is one minor problem.. i do not want the microseconds.. i want only till the seconds..
    Can somebody help me on that..

    Thanks!!!!

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    Code:
    DECLARE @A VARCHAR(10)
    SET @A='9:00 am'
    SELECT CAST(@A AS DATETIME) AS SAMPLE1
    SELECT CAST(CAST(@A AS DATETIME) AS VARCHAR(34))AS SAMPLE2
    SELECT CONVERT(VARCHAR(80), CAST(@A AS DATETIME), 120) AS SAMPLE3
    Code:
    SAMPLE1
    -----------------------
    1900-01-01 09:00:00.000
    
    (1 row(s) affected)
    
    SAMPLE2
    ----------------------------------
    Jan  1 1900  9:00AM
    
    (1 row(s) affected)
    
    SAMPLE3
    --------------------------------------------------------------------------------
    1900-01-01 09:00:00
    
    (1 row(s) affected)


    SQL Server Helper - Tips and Tricks - Date Formats
    Last edited by mishaalsy; 01-06-11 at 09:10.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The time datatype was introduced in SQL 2008. What version are you using?

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    mac4rfree ! I am yet using sqlserver2005 . I am sorry if my ignorance have caused u any trouble

  7. #7
    Join Date
    Jul 2009
    Posts
    58
    Thanks guys, as of now.. i will stick with the nanoseconds..

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @c VARCHAR(9)
    
    SET @c = '9:00 am'
    
    SELECT CONVERT(Char(8), Convert(DATETIME, @c), 8)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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