none
how+to+convert+nvarchar+to+datetime+in+sql

    Question

  • Hi All;

    In my below query i need to display dates in the format Apr-09,Dec-09,May-11

    in ascending order but it doesnt work

    Any help much appreciated

    Thanks

    SELECT CONVERT(nvarchar, new_claimperiodidname, 103) 
    FROM (
    SELECT DISTINCT new_claimperiodidname
    FROM filterednew_evidence) AS T
    where new_claimperiodidname is not null
    ORDER BY CONVERT(nvarchar, REPLACE(new_claimperiodidname, '-', ' 01 '), 100);


    Pradnya07

    Thursday, February 28, 2013 10:52 AM

Answers

  • i got an error

    Msg 241, Level 16, State 1, Line 2

    Conversion failed when converting date and/or time from character string.


    Pradnya07

    Are you running Santhosh's query against your live data?  Because I copy/pasted his query directly into an SQL 2008 instance, and it worked.  If you are running against live data and getting the error, could you give us a sample of said data?

    Also, it is possible that some of the fields contain bad data.  Is the datatype of the field datetime (or similar)?  If they are not stored as date, run a query to find the values that are not valid dates:

    Select	new_claimperiodidname
    From	filterednew_evidence
    Where	IsDate(new_claimperiodidname) <> 1

    Thursday, February 28, 2013 2:21 PM

All replies

  • try this :

    DECLARE @table TABLE(Dt DATE)
    
    INSERT INTO @table
    VALUES('04-01-2009'),('12-01-2009'),('05-01-2011'),('11-01-11'),('02-01-2009')
    
    
    
    select LEFT(CONVERT(nvarchar, dt ,107),3) + '-' + RIGHT(CONVERT(nvarchar, dt,107),2) dt1
    from @table
    order by dt1 desc


    Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, February 28, 2013 11:07 AM
  • SELECT CONVERT(nvarchar, new_claimperiodidname, 103) 
    FROM (
    SELECT DISTINCT new_claimperiodidname
    FROM filterednew_evidence) AS T
    where new_claimperiodidname is not null
    ORDER BY CONVERT(DateTime,new_claimperiodidname);


    Please have look on the comment

    Thursday, February 28, 2013 11:14 AM
  • Hi Santosh

    Thanks for your reply

    i received the following error

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.

    Thanks

    Pradnya


    Pradnya07

    Thursday, February 28, 2013 11:19 AM
  • can u please paste few examples for same .

    Please have look on the comment

    Thursday, February 28, 2013 11:26 AM
  • Hi Paresh,

    Thanks for your reply

    but when i run the below code i get the same old output

    as below

    dt1

    Apr-09

    Apr-10

    Apr-11

    Aug-09

    i want in date asccending order as

    Apr -09

    Aug-09

    Apr-10 and so on

    SELECT LEFT(CONVERT(nvarchar, new_claimperiodidname ,107),3) + '-' + RIGHT(CONVERT(nvarchar, new_claimperiodidname,107),2) dt1
    FROM (
    SELECT DISTINCT new_claimperiodidname
    FROM filterednew_evidence) AS T
    where new_claimperiodidname is not null
    ORDER BY dt1


    Pradnya07

    Thursday, February 28, 2013 11:26 AM
  • Is it something like this u need irrespective of year, the result will be date & month wise ordered (ascending)

    DECLARE @table TABLE(Dt DATEtime)

    INSERT INTO @table
    VALUES('04-01-2009'),('12-01-2009'),('05-01-2011'),('11-01-11'),('02-01-2009')



    select LEFT(CONVERT(nvarchar, dt ,107),3) + '-' + RIGHT(CONVERT(nvarchar, dt,107),2) dt1,dt
    from @table
    order by Month(dt)

    If u consider even the year then

    change order by to just the columnname of that Datetime datat type, for above example it would be just

    Order By dt


    Please have look on the comment




    • Edited by Santhosh H Thursday, February 28, 2013 11:51 AM
    Thursday, February 28, 2013 11:30 AM
  • Hi Paresh,

    Thanks for your reply

    but when i run the below code i get the same old output

    as below

    dt1

    Apr-09

    Apr-10

    Apr-11

    Aug-09

    i want in date asccending order as

    Apr -09

    Aug-09

    Apr-10 and so on

    SELECT LEFT(CONVERT(nvarchar, new_claimperiodidname ,107),3) + '-' + RIGHT(CONVERT(nvarchar, new_claimperiodidname,107),2) dt1
    FROM (
    SELECT DISTINCT new_claimperiodidname
    FROM filterednew_evidence) AS T
    where new_claimperiodidname is not null
    ORDER BY dt1


    Pradnya07

    try this :

    SELECT LEFT(CONVERT(nvarchar, new_claimperiodidname ,107),3) + '-' + RIGHT(CONVERT(nvarchar, new_claimperiodidname,107),2) dt1
    FROM (
    SELECT DISTINCT new_claimperiodidname
    FROM filterednew_evidence) AS T
    where new_claimperiodidname is not null
    ORDER BY new_claimperiodidname 


    Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, February 28, 2013 11:44 AM
  • Please try the below tsql - 

    DECLARE @table TABLE(Dt DATE) INSERT INTO @table VALUES('04-01-2009'),('12-01-2009'),('05-01-2011'),('11-01-11'),('02-01-2009') SELECT LEFT(CONVERT(VARCHAR,Dt,109),3) + ' - ' + RIGHT(CONVERT(VARCHAR,Dt,109),2) Way1,

    LEFT(DATENAME(MM,Dt),3) + ' - ' + RIGHT(CONVERT(VARCHAR,YEAR(dt)),2) Way2

    FROM @table ORDER BY dt


    Hope, this helps!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    • Edited by vinaypugalia Thursday, February 28, 2013 12:00 PM
    Thursday, February 28, 2013 11:58 AM
  • i got an error

    Msg 241, Level 16, State 1, Line 2

    Conversion failed when converting date and/or time from character string.


    Pradnya07

    Thursday, February 28, 2013 12:39 PM
  • it didnt work

    Pradnya07

    Thursday, February 28, 2013 12:40 PM
  • i got an error

    Msg 241, Level 16, State 1, Line 2

    Conversion failed when converting date and/or time from character string.


    Pradnya07

    Are you running Santhosh's query against your live data?  Because I copy/pasted his query directly into an SQL 2008 instance, and it worked.  If you are running against live data and getting the error, could you give us a sample of said data?

    Also, it is possible that some of the fields contain bad data.  Is the datatype of the field datetime (or similar)?  If they are not stored as date, run a query to find the values that are not valid dates:

    Select	new_claimperiodidname
    From	filterednew_evidence
    Where	IsDate(new_claimperiodidname) <> 1

    Thursday, February 28, 2013 2:21 PM
  • SELECT Replace(RIGHT(CONVERT(varchar(20),new_claimperiodidname, 6),6),' ', '-') as newDT
    FROM (
    SELECT DISTINCT new_claimperiodidname
    FROM filterednew_evidence) AS T
    where new_claimperiodidname is not null
    ORDER BY new_claimperiodidname;

    Thursday, February 28, 2013 5:08 PM
  • Please check if the data in your actual table contains a correct DateTime value which is being converted. If not, please correct the data and then run this query.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Friday, March 01, 2013 4:18 AM