none
Convert NVarchar to DateTime - SQL Server 2000

    Question

  • Hi there,

    FROM USER INTERFACE END USER SELECTS START MONTH, START YEAR AND END MONTH, END YEAR. IT IS REQUIRED TO RETRIEVE DATA WHERE ONE OF THE FIELDS I.E, ExpiryDate is of type NVARCHAR, stored dates as 2/10/2010 [DD/MM/YYYY] , 10/10/2010 ETC.

    Using SQL Server 2000.

    ALTER PROCEDURE [dbo].[CountUsers]
    		(
    			@startMonth nvarchar(20) =	NULL,
    			@startYear nvarchar(20) = NULL,
    			@endMonth  nvarchar(20) = NULL,
    			@endYear  nvarchar(20) = NULL,
    			@startDate nvarchar(50) = NULL,
    			@endDate  nvarchar(50) = NULL
    		)
    		AS BEGIN
    
    		SET @startDate = @startYear + '-01-' + @startMonth; 
    		SET @endDate  = @endYear + '-01-' + @endMonth; 
    
    SELECT CASE 
    		WHEN month(a.registrationdate)=1 THEN 'Jan'
    		WHEN month(a.registrationdate)=2 THEN 'Feb'
    		WHEN month(a.registrationdate)=3 THEN 'Mar'
    		WHEN month(a.registrationdate)=4 THEN 'Apr'
    		WHEN month(a.registrationdate)=5 THEN 'May'
    		WHEN month(a.registrationdate)=6 THEN 'Jun'
    		WHEN month(a.registrationdate)=7 THEN 'Jul'
    		WHEN month(a.registrationdate)=8 THEN 'Aug'
    		WHEN month(a.registrationdate)=9 THEN 'Sep'
    		WHEN month(a.registrationdate)=10 THEN 'Oct'
    		WHEN month(a.registrationdate)=11 THEN 'Nov'
    		WHEN month(a.registrationdate)=12 THEN 'Dec' END AS Month,
    		YEAR(a.registrationdate) as Year,
    		b.organisationid as InstitutionID, b.name AS Institution_Name,b.country as Country, 		
    SUM(case when a.FromID IN('Microsoft') then 1 ELSE 0 END) AS No_Of_Registrations_Via_Microsoft,
    		SUM(case when a.FromID IN('Java') then 1 ELSE 0 END) AS No_Of_Registrations_Java,
    --Need help HERE TO COUNT EXPIRED
    SUM(CASE WHEN CONVERT(DATETIME,ExpiryDate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND 
    		CONVERT(DATETIME,ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired		FROM users a inner join organisation b ON a.organisationid=b.organisationid
    		WHERE a.registrationdate > = ( CONVERT(datetime, @startDate , 103)) and registrationdate<dateadd(month,1,(convert(datetime,@endDate, 103)))
    		GROUP BY b.organisationid, b.name,b.country,month(a.registrationdate),year(a.registrationdate)
    		ORDER BY year(a.registrationdate),month(a.registrationdate),b.name
    
    END
    

    Apriori algorithm [association rule]
    Tuesday, October 19, 2010 11:46 PM

Answers

  • Thanks Olaf Helper. Here is the solution I adopted. I am sure that there are more solutions out there.

    There are few incorrect dates in ExpiryDate column (NVarchar), after updating those DATES. Below SQL statement converts NVARCHAR to DateTime.

    convert(datetime,right(a.expirydate,4)+'-'+ '01' + '-' + replace(substring(a.expirydate,charindex('/',expirydate)+1,2),'/',''),103) between CONVERT(datetime, @StartDate, 103) and CONVERT(datetime, @EndDate, 103)
    

     


    Apriori algorithm [association rule]
    • Marked as answer by Sukumar Raju Friday, October 22, 2010 7:50 AM
    Friday, October 22, 2010 7:49 AM

All replies

  • ExpiryDate column in Table Data Type is NVARCHAR , storing Dates ONLY without time as 2/10/2010 , 12/10/2010 IN DD/MM/YYYY FORMAT.

    NOW IT IS REQUIERD TO RETRIEVE ALL RECORDS WHERE EXPIRYDATE BETWEEN USER PASSED START MONTH,START YEAR AND END MONTH, END YEAR.

    USING SQL SERVER 2000

    SUM(CASE WHEN CONVERT(datetime,a.expirydate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND 
    		CONVERT(DATETIME,a.ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired
    
    

     

    Your help is greatly appreciated. Spent hours to get this resolved.


    Apriori algorithm [association rule]
    • Merged by KJian_ Wednesday, October 20, 2010 5:18 AM
    Wednesday, October 20, 2010 12:23 AM
  • SET @startDate = @startYear + '-01-' + @startMonth;

    SET @endDate = @endYear + '-01-' + @endMonth;


     

    Hi,

    Could you please elaborate a bit on your scenario?

    If you want to build the @startDate and @endDate in the ISO format (yyyy-mm-dd), I think it should be changed to:

    SET @startDate = @startYear + '-' + @startMonth + '-01'; 
    SET @endDate = @endYear + '-' + @endMonth + '-01'; 
    

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 20, 2010 2:53 AM
  • Hello,

    A little hint: Instead of the CASE WHEN statement for the short month name you could use one of these statements:

    SELECT LEFT(DATENAME(mm, GetDate()), 3) AS MonthShort

    SELECT CONVERT(varchar(3), GetDate(), 100) AS MonthShort

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, October 20, 2010 4:15 AM
  • Hello,

    Try it with this statement: It uses a fixed date to calculate start & end date with the given values for start/end month/year.

    DECLARE @StartYear int, @StartMonth int;

    DECLARE @EndYear int, @EndMonth int;

     

    SET @StartYear = 2010

    SET @EndYear = 2010

    SET @StartMonth = 10

    SET @EndMonth = 11

     

    SELECT CONVERT(datetime, tmp.CharDate, 103) AS RealDate

    FROM (SELECT '2/9/2010' AS CharDate

          UNION ALL

          SELECT '12/10/2010'

          UNION ALL

          SELECT '12/11/2010') AS Tmp

    WHERE CONVERT(datetime, tmp.CharDate, 103)

          BETWEEN

          DATEADD(mm, @StartMonth -1,

                      DATEADD(yyyy, @StartYear - 2010, '2010-01-01'))

          AND

          DATEADD(dd, -1,

                      DATEADD(mm, @EndMonth,

                              DATEADD(yyyy, @EndYear - 2010, '2010-01-01')))

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Wednesday, October 20, 2010 4:31 AM
  • Thanks Olaf Helper. Here is the solution I adopted. I am sure that there are more solutions out there.

    There are few incorrect dates in ExpiryDate column (NVarchar), after updating those DATES. Below SQL statement converts NVARCHAR to DateTime.

    convert(datetime,right(a.expirydate,4)+'-'+ '01' + '-' + replace(substring(a.expirydate,charindex('/',expirydate)+1,2),'/',''),103) between CONVERT(datetime, @StartDate, 103) and CONVERT(datetime, @EndDate, 103)
    

     


    Apriori algorithm [association rule]
    • Marked as answer by Sukumar Raju Friday, October 22, 2010 7:50 AM
    Friday, October 22, 2010 7:49 AM