to_date in SQL Server 2005

Question

Does any one know how I would have to change the following to work with ms sql?

WHERE registrationDate between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

What I have read implies I would have to construct it using DATEPART() which could become very long winded. Especially when the goal would be to compare on dates which I receive in the following format "2003-12-30 10:07:42". It would be nice to pass them off to the database as is.

Any pointers appreciated.

OMG Ponies · Accepted Answer · 2012-08-19T23:11:58.140

2

Use:

WHERE registrationdate BETWEEN '01/01/2003' AND '12/31/2003'

...but as gbn pointed out, to be locale safe - use:

WHERE registrationdate BETWEEN '20030101' AND '20031231'

SQL Server will perform implicit conversion of the string into a date, providing it's a supported format. Explicit conversion is when you have to use CAST or CONVERT to change the data type.

When converting '01/01/2003' to a DATETIME, the time portion will be 00:00:00 because it wasn't specified.

edited Aug 19 '12 at 23:11

answered Apr 03 '10 at 02:08

OMG Ponies

325,700
82
523
502

Thats saves me a heap of time, Muchus gracias – Chin Apr 03 '10 at 02:35
1

@Chin beware your edge cases as the code above would not return a record with "2003-12-31 10:07:42" in it. With no time included, it is assumed to be 0:00:00. You might want to use '01/01/2004' or '12/31/2003 23:59:59' – SqlACID Apr 03 '10 at 02:49
2

Using an explicit format `CONVERT(datetime, '01/01/2003', 103)` will save you a lot of potential problems. – erikkallen Apr 03 '10 at 09:26
4

I would use ISO and safe 20030101 and 20011231 to be locale independent – gbn Apr 03 '10 at 09:50

score 1 · Answer 2 · answered Feb 27 '13 at 19:50

CREATE FUNCTION [DBO].[to_date](@P_FORMAT VARCHAR(30), 
                                @P_STRING VARCHAR(30)) 
RETURNS DATETIME 
AS 
  BEGIN 
      DECLARE @V_DAY INT 
      DECLARE @V_MONTH INT 
      DECLARE @V_YEAR INT 
      DECLARE @V_HOUR INT 
      DECLARE @V_MINUTE INT 
      DECLARE @V_SECOND INT 
      DECLARE @V_DATETIME DATETIME 

      SET @V_DAY = charindex('dd', @P_FORMAT) 
      SET @V_MONTH = charindex('mm', @P_FORMAT) 
      SET @V_YEAR = charindex('yyyy', @P_FORMAT) 
      SET @V_HOUR = charindex('hh', @P_FORMAT); 
      SET @V_MINUTE = charindex('nn', @P_FORMAT); 
      SET @V_SECOND = charindex('ss', @P_FORMAT); 

      IF @V_DAY = 0 
        SET @V_DAY=1 
      ELSE 
        SET @V_DAY=CONVERT(INT, substring(@P_STRING, @V_DAY, 2)); 

      IF @V_MONTH = 0 
        SET @V_MONTH=1 
      ELSE 
        SET @V_MONTH=CONVERT(INT, substring(@P_STRING, @V_MONTH, 2)); 

      IF @V_YEAR = 0 
        SET @V_YEAR=2000 
      ELSE 
        SET @V_YEAR=CONVERT(INT, substring(@P_STRING, @V_YEAR, 4)); 

      IF @V_HOUR = 0 
        SET @V_HOUR=0 
      ELSE 
        SET @V_HOUR=CONVERT(INT, substring(@P_STRING, @V_HOUR, 2)); 

      IF @V_MINUTE = 0 
        SET @V_MINUTE=0 
      ELSE 
        SET @V_MINUTE=CONVERT(INT, substring(@P_STRING, @V_MINUTE, 2)); 

      IF @V_SECOND = 0 
        SET @V_SECOND=0 
      ELSE 
        SET @V_SECOND=CONVERT(INT, substring(@P_STRING, @V_SECOND, 2)); 

      SET @V_DATETIME=CONVERT(DATETIME, cast(@V_YEAR AS VARCHAR) + '-' 
                                        + cast(@V_MONTH AS VARCHAR) + '-' 
                                        + cast(@V_DAY AS VARCHAR) + ' ' 
                                        + cast(@V_HOUR AS VARCHAR) + ':' 
                                        + cast(@V_MINUTE AS VARCHAR) + ':' 
                                        + cast(@V_SECOND AS VARCHAR), 120); 

      RETURN @V_DATETIME; 
  END

to_date in SQL Server 2005

2 Answers2

Linked