-1

I am getting an error and I am using SQL Server 2008 

select * from  where rd_date between 
to_date('2018-17-05 00:00:00') and  to_date('2018-06-06 00:00:00')

When I execute, I am getting the below error

Msg 195, Level 15, State 10, Line 68 'to_date' is not a recognized built-in function name.

MSM
  • 327
  • 2
  • 6
  • 12
  • 3
    Where in the [SQL Server Manual](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb510741(v=sql.105)) did you find `to_date`? –  Aug 14 '18 at 05:54
  • Where possible, **don't** pass datetime information to SQL Server as *strings*. If this is coming from some other programming language, seriously look into whether there's a mechanism (usually called parameters) for passing whatever your programming language uses for dates to SQL Server *without* having to convert them into strings and then forcing SQL Server to convert them back. – Damien_The_Unbeliever Aug 14 '18 at 05:57
  • And if you're typing these strings into e.g. management studio, please use `YYYY-MM-DD'T'hh:mm:ss` format. That format will always be *unambiguously* interpreted by SQL Server correctly, no matter what language or date format settings are in force. – Damien_The_Unbeliever Aug 14 '18 at 05:59

4 Answers4

4

To_date is not a function in sql server: You can try below

 select * from where rd_date between cast('2018-17-05 00:00:00' as date) and cast('2018-06-06 00:00:00' as date)

OR

SELECT select * from where rd_date between CONVERT(DATETIME, '2018-17-05 00:00:00', 102) and CONVERT(DATETIME,'2018-06-06 00:00:00')

OR

TRY_CONVERT available since SQL Server 2012 (returns NULL if conversion fails)

SELECT select * from where rd_date between TRY_CONVERT(DATETIME, '2018-17-05 00:00:00', 102) and TRY_CONVERT(DATETIME,'2018-06-06 00:00:00')
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • 1
    I've given three method you can try any of this – Fahmi Aug 14 '18 at 04:51
  • Thanks, i am getting the below error when i execute the 2 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. – MSM Aug 14 '18 at 04:52
  • without use convert can u use just cast function – Fahmi Aug 14 '18 at 04:58
  • 2
    The above statements don't work, because the last option `102` requires ansi format of the date string, which is `yyyy.mm.dd`. The separator may be exchanged by a slash or a dash. Either change the order of `mm` and `dd` or use 101 (us-format). – Jürgen Röhr Aug 14 '18 at 06:09
  • Thanks, the date format in the tables is 'mm/dd/yyyy hh:mm:ss' – MSM Aug 14 '18 at 06:17
  • @MSM - hopefully not. If the database is properly designed, that column should be `datetime` or `datetime2`, in which case there *is* no format. What you're describing as the "format" is just what management studio happens to do if it's trying to display results on screen, at which point it has to convert the `datetime` into a string for display, and that's the format the *management studio* is applying during the conversion. – Damien_The_Unbeliever Aug 14 '18 at 07:05
0

Your format is very custom and is not supported in CONVERT function (which you can use to some specific date formats, as presented in the other answer). Thus, you need to do some string operations to get correct format, that can be recognized by SQL. Try below query:

select cast(
           substring(col,1,5) + substring(col,9,2) + substring(col,5,3) + substring(col, 11, 20)
       as datetime)
from (values('2018-17-05 00:00:00'),('2018-06-06 00:00:00')) a(col)
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
0

@MSM, Try this. Please replace TABLENAME with the actual table you are querying. You dont need 'to_date' as you might not have that function already defined.

SELECT * 
FROM
  TABLENAME
where rd_date between CAST('20180517 10:35:21.177' AS DATE)  AND CAST('20180606 10:35:21.177' as DATE)
MEdwin
  • 2,940
  • 1
  • 14
  • 27
0

Perhaps not a good idea reading the other answers, but I always write hardcoded dates simply as 'yyyy-mm-dd' strings and I always was happy with that.
In your example: 

select * from  where rd_date between '2018-17-05' and  '2018-06-06'

Now I would never user Between for such a case, but rather >= and < which are much clearer when your field might contain time part.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • 1
    hyphenated-without-time was problematic on older SQL Server versions where it could still take it as `YYYY-DD-MM` instead. The classic safe formats were `YYYYMMDD`, `YYYY-MM-DD'T'hh:mm:ss` and `YYYY-MM-DD'T'hh:mm:ss.mil` – Damien_The_Unbeliever Aug 14 '18 at 09:49