-5

This is the line.

DECLARE @Duration DATETIME = '2019-01-12'
DECLARE @DateFrom DATETIME = CONVERT(DATETIME, DATEADD(dd, -90, CONVERT(VARCHAR(10), @Duration)))
SELECT @DateFrom

This is the error message

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

and the sql results is NULL but 1 row affected.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Arvin John Salandanan
  • 49
  • 6
  • 1
    [dateadd](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15) – Dale K Oct 28 '19 at 09:15
  • 1
    Expected output ? – Amira Bedhiafi Oct 28 '19 at 09:16
  • 5
    As a rule of thumb, the last thing you want to do with dates is to convert them to or from strings. – Zohar Peled Oct 28 '19 at 09:40
  • Hi. This line works perfectly in SQL. But when i put the query on Delphi the sql statement doesnt work. DECLARE @DateFrom DATETIME = CONVERT(DATETIME, DATEADD(dd, -90, CONVERT(VARCHAR(10), '2019-01-12'))) – Arvin John Salandanan Oct 28 '19 at 09:48
  • What do you mean by "doesn't work"? Does it give the wrong result? Does it throw an error? Please show the code with sample data, actual result and expected result. – Dale K Oct 28 '19 at 09:50
  • 2
    @ArvinJohnSalandanan you are using this query in Delphi, can you please update your question properly ? – Amira Bedhiafi Oct 28 '19 at 09:57
  • if it works in SQL and not in Deplhi then it would appear to be a Delphi issue. I've tagged Delphi for you. – Nick.Mc Oct 28 '19 at 09:58
  • Sorry. If it works in MS SQL I think I can a find a way to implement it in Delphi. First, I want to convert a DateTime variable datatype to string so I can subtract a days (90days) Then convert it again to DateTime format after subtracting (90days). – Arvin John Salandanan Oct 28 '19 at 10:00
  • Possible duplicate of [SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value](https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in) – Amira Bedhiafi Oct 28 '19 at 10:54
  • 1
    You repeatedly say that you have a problem when you implement it in Delphi. Yet you have not shown how you have tried to implement it in Delphi!. So please, show the complete Delphi procedure code (including variable declarations) that you have tried, and indicate the line where an error is triggered, and what the error exactly is. Further as others have already said, you don't need, and you should not convert the date and time to string in order to modify the date. – Tom Brunberg Oct 28 '19 at 12:53
  • The only time you ever, ever want a date to be in a string is when you're displaying it to the user. Otherwise, don't even think about making it a string. – Jerry Dodge Oct 30 '19 at 21:20

3 Answers3

4

Stop using CONVERT() to begin with, you don't need it!

DATEADD() can take in a DATETIME variable as-is and return a DATETIME:

DECLARE @Duration DATETIME = '2019-01-12'
DECLARE @DateFrom DATETIME = DATEADD(dd, -90, @Duration)
SELECT @DateFrom

It can also take in a date/time string literal and returns a DATETIME:

DECLARE @DateFrom DATETIME = DATEADD(dd, -90, '2019-01-12')
SELECT @DateFrom
Remy Lebeau
  • 555,201
  • 31
  • 458
  • 770
0

Using DATEADD function, your query would be something like this depending on what you afforded in your problem :

DECLARE @Date datetime2 = '2011-09-23 15:48:39.2370000'

SELECT DATEADD(day,-90,@Date)
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • Hi. This line works perfectly in SQL. But when i put the query on Delphi the sql statement doesnt work. DECLARE @DateFrom DATETIME = CONVERT(DATETIME, DATEADD(dd, -90, CONVERT(VARCHAR(10), '2019-01-12'))) – Arvin John Salandanan Oct 28 '19 at 09:49
-3
DECLARE @DateVariable DATETIME
SET @DateVariable = '2019-01-12'
DECLARE @DateFrom DATETIME = CONVERT(DATETIME, DATEADD(dd, -90, CONVERT(VARCHAR(10), @DateVariable,103)))
SELECT @DateFrom

I find an answer here. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).

SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
Arvin John Salandanan
  • 49
  • 6
  • 1
    No - the problem is that you chose the wrong path and converted to string for this logic. There is no need to convert to string at all - and certainly not twice. Nor was the use of datetime variables appropriate based on the code provided. You are using dates and you are manipulating them at the day unit level - the time does not change and never enters into your calculation at all. Just remove the use of convert - both of them. – SMor Oct 28 '19 at 12:20