Date won't change after using DATEADD() function in SQL Server 2017

Question

I have a date stored in a string (@cnp=1051597991234) the date being 051597 representing 5/15/1997. I am using the following code to set a date variable with the needed value, in the end to compute the current age.

I used the debugger but somehow it ends up being not set and the diff also returning null.

If someone could help I would be very grateful

declare @cnp varchar(30) = 1051597991234;
declare @age int;
declare @dateBorn date = CAST('1900-01-01' AS DATETIME);

declare @dayBorn int;
declare @monthBorn int;
declare @yearBorn int;

set @dayBorn = cast(substring(@cnp, 2, 2) as int) - 1;
set @monthBorn = cast(substring(@cnp, 4, 2) as int) - 1;
set @yearBorn = cast(substring(@cnp, 6, 2) as int);

set @dateBorn = dateadd(yyyy, @yearBorn, @dateBorn);
set @dateBorn = dateadd(mm, @monthBorn, @dateBorn);
set @dateBorn = dateadd(dd, @dayBorn, @dateBorn);

set @age = datediff(year, getdate(), @dateBorn);

You know you're getting a syntax error for your first line, right? Fix that? *(missing the datatype : `NVARCHAR(32)` or similar?)* — MatBailie, Dec 30 '17 at 12:25
then you're doing something else wrong and you should include the whole procedure that's failing : http://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=0dc0690384ddd81005001d7c09462c01 — MatBailie, Dec 30 '17 at 12:46
i included the part that is failing, i checked with the debugger and everything sets fine until this point — rednefed, Dec 30 '17 at 12:48
See my link to dbfiddle in the comments above : If you fix the first line, everything else works. *(After your edit, the script you have provided executes just fine, though with a negative age. The issue you are facing is not in that code snippet.)* — MatBailie, Dec 30 '17 at 12:49
yeah sorry, sql server doesn't make any sense but, i found the mistake after the code fixed itself(???). Its in the datediff function the parameter are in the wrong order its supposed to be datediff(year,@dateBorn, getdate() ); even tho you compute the difference between getdate and dateborn and not dateborn and getdate. thanks for the help — rednefed, Dec 30 '17 at 13:00
The code did not fix itself. Don't be insane. Something else changed. Deterministic results don't change without the input, the code, or the environment changing. — MatBailie, Dec 30 '17 at 13:04
Side note: this is a terrible way to store a date. First, not only is it not one of the many `Date`-related types in SQL Server (most of which will be _smaller_ in size), it's also not SARGable, meaning searching on this as a date is going to be painful, and somewhat useless. — Clockwork-Muse, Dec 30 '17 at 17:07

score 1 · Answer 1 · answered Dec 30 '17 at 12:31

Can I suggest using the DATEFROMPARTS function?

So:

DECLARE @dateBorn DATE = 
    DATEFROMPARTS(
        CAST('19' + SUBSTRING(@cnp,6,2) AS INT)
        ,CAST(SUBSTRING(@cnp,2,2) AS INT)
        ,CAST(SUBSTRING(@cnp,4,2) AS INT)
        );

DECLARE @age INT = 
    DATEDIFF(YEAR, GETDATE(), @dateBorn);

Please note that the birthdate strings you have are not Y2K safe, which is why I've put a constant in to assume that they were in the 20th century.

Also note that your dates are in American format, with the month first, day second, and year third.

thanks but i am getting this error "Cannot construct data type date, some of the arguments have values which are not valid." — rednefed, Dec 30 '17 at 12:42

score 1 · Accepted Answer · answered Dec 30 '17 at 12:39

1

It's declaration problem. Just change this

declare @cnp = 1051597991234

with this

declare @cnp nvarchar(20) ='1051597991234'

and also change the sequence for as from date will be date of birth

set @age =datediff(year,@dateBorn,getdate());

answered Dec 30 '17 at 12:39

user3551009

174
6

Date won't change after using DATEADD() function in SQL Server 2017

2 Answers2