Results 1 to 15 of 15

Thread: MSSQL Server:conversion of char or varchar to datetime type

  1. #1
    Join Date
    Jul 2006
    Posts
    5

    MSSQL Server:conversion of char or varchar to datetime type

    Hi

    I have a problem while converting the char to datetime type in MSSQL Server 200/2005.

    I want to retrieve the date values from table in datetime data type in a particular format like 'yyyydd' or 'yyyy/dd' similar to oracle's TO_DATE function.

    For this I tried with following query,

    SELECT CONVERT(datetime, CONVERT(varchar(6), date1, 112), 112) from sql_dm1

    But I got the following error while executing the above query:
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Please help me in this regard.

    Thanks
    Sridhar

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You may just need this one:

    SELECT CONVERT(varchar(6), date1, 112) from sql_dm1

  3. #3
    Join Date
    Jul 2006
    Posts
    5
    Datatype of return value from the above function is varchar/char but my requirement is, it should be Datetime datatype.

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    If your data is only 'yyyydd' or 'yyyy/dd', what month is it? It's going to be pretty hard to convert that data without knowing what month it is.

  5. #5
    Join Date
    Jul 2006
    Posts
    5
    its dynamic, I mean the values stored in the database are multiple rows.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Can you post some sample data and the result you like to see?

  7. #7
    Join Date
    Jul 2006
    Posts
    5
    I have the table with following data:

    date1

    2006-04-13 00:00:00
    2006-12-21 00:00:00
    2006-01-25 00:00:00
    2006-10-27 00:00:00
    2007-08-29 00:00:00

    And I want the result should be :

    2006-04
    2006-12
    2006-01
    2006-10
    2007-08

    By using SELECT CONVERT(varchar(7), date1, 112) from sql_dm1 I can get the above result, but the return value is now string type not Date datatype. If I apply sorting on it sorting is done on string not Date datatype. This is the actual problem I am facing.

  8. #8
    Join Date
    Dec 2004
    Posts
    502
    You can't have the results you want with a datetime datatype. The datetime datatype requires all elements of a date (year, month, and day). And I don't see what's the difference between sorting your results as string vs. date. The results should be the same. I think you're going to have to explain what you want to do in more detail.

  9. #9
    Join Date
    Jul 2006
    Posts
    5
    I am using the results in a web application. The user can sort the results, as the result values are in string type the values sorted are: first comes april then aug then february then january.....
    not by month.

    This is the problem I am having in the application.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Where do you sort result? Didn't see your issue in sql result with order by clause.

  11. #11
    Join Date
    Dec 2004
    Posts
    502
    Show us EXACTLY the data you are trying to convert to datetime. Looks like you are starting with month names ("January" instead of "01"). Please give us more information, or else you make it hard for us to help you.

  12. #12
    Join Date
    Jul 2006
    Posts
    1

    Its not possible with datetime type

    The datetime type by default needs all the three parts. The alternative could be you get the data in whatever format SQL gives you, then in the front-end you can convert and sort it the way you need

  13. #13
    Join Date
    Aug 2006
    Location
    Bangalore
    Posts
    5
    Hi Srimamidi,
    try this query..
    SELECT CAST(YEAR(DATE1) AS VARCHAR(4)) + '-' + CAST(MONTH(DATE1) AS VARCHAR(2)) AS DATE1 FROM TABLE_NAME

  14. #14
    Join Date
    Jul 2009
    Posts
    1

    Convert row from varchar to datetime

    Hy,

    I have a few table and one of the row is date but in in varchar format.(now the format is mm/dd/yyyy).

    Now i want to convert the row in datetime format mssql but like this(dd.mm.yyyy)
    I can change this?

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    Possible with style 104, take look at 'CAST and CONVERT (Transact-SQL)' in books online.

Posting Permissions

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