database engineer

elucero

<div>
Thanks that gives me 1800-01-01, how would I convert -58438 and 2647911
</div>


<div>
The same way. &nbsp;A neg date is one before Jan 1, 1960.<br><br>Is it a SAS date or a datetime?
</div>


<div>
it's a datetime, so -58438 is about 160 years subtracted from 19600101, so it would be 1800-01-01 and 2647911 is 7254 years added on to 19600101, so these 2 values are trash data. &nbsp;I'm scrubbing data from a large sas file and these were my min and max birthdate values.&nbsp;&nbsp;
</div>


<div>
OK, a datetime is # of seconds, so:<br><br>DATEADD(<strong>SECOND</strong>, date_of_birth, '19600101') AS BirthDate
</div>


<div>
thany you!<br>ok now I'm a little bit confused,<br>so what are these values then?&nbsp;&nbsp;<br>&nbsp;-58438<br>2647911 <br><br>
</div>


<div>
thanks,<br>they are the only 2 values I'm getting when I use DATEADD( <strong>SECOND</strong>, date_of_birth, '19600101') AS BirthDate<br><br>the values -58438 and 2647911 &nbsp; are my min and max date_of_birth values<br><br>I have other date_of_birth values<br>like<br>&nbsp;-1152<br>6980<br>-4639<br>Their all coming up at 1959-12-31 07:46:02.000 &nbsp;or 1960-01-31 15:31:51.000&nbsp;
</div>


<div>
That doesn't make sense, something's not right.<br><br>I'd need to see the <em>exact</em> code to try to figure that one out.
</div>


<div>
--source table starts off as the date_of_birth being a float<br>Declare @birthtable as table (date_of_birth float)<br>Insert into &nbsp;@birthtable<br>select '-1152'<br>union<br>select '6980'<br>union<br>select '-4639'<br>union<br>select '-58438'<br>union<br>select '2647911'<br><br><br><br>select date_of_birth, dateadd(day,date_of_birth,'19600101'),dateadd(second,date_of_birth,'19600101'),<br>--the final step is I have to load into a table that's a varchar(100) in this format<br>convert(varchar(100),(dateadd(second,date_of_birth,'19600101')),121)<br>from @birthtable
</div>


<div>
I'm not sure what else you want <em>me</em> to do? &nbsp;The date should be displaced from Jan 1, 1960. &nbsp;Neg numbers are before that date, positive numbers after.<br><br>As to what your specific data in the table is, I can't really know. &nbsp;That's an internal data issue for you to resolve, I can't help with that.
</div>


<div>
thanks I got it, it was crap data.And the rest converted correctly.
</div>


<div>
<div class="content wysiwyg-content fr-view">
I'm trying to convert the following dates into sql from sas, both are coming up incorrect. &nbsp;Any suggestions?<br><br>CASE&nbsp;WHEN&nbsp;date_of_birth&nbsp;&lt;0&nbsp;THEN&nbsp;DATEADD(ss,21914,CAST(ABS(date_of_birth)&nbsp;AS&nbsp;DATETIME))<br>ELSE&nbsp;DATEADD(ss,1893369600,CAST([date_of_birth]&nbsp;AS&nbsp;DATETIME)&nbsp;)&nbsp;END&nbsp;as&nbsp;BirthDate<br>date_of_birth in('-58438','2647911')<br><br>date_of_birth&nbsp; &nbsp;result<br>-58438 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2059-12-31 06:05:14.000<br>2647911 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9209-09-25 00:00:00.000<br><br>
</div>
</div>



I'm trying to convert the following dates into sql from sas, both are coming up incorrect.  Any suggestions?
CASE WHEN date_of_birth <0 THEN DATEADD(ss,21914,CAST(ABS(date_of_birth) AS DATETIME))
ELSE DATEADD(ss,1893369600,CAST([date_of_birth] AS DATETIME) ) END as BirthDate
date_of_birth in('-58438','2647911')
date_of_birth   result
-58438             2059-12-31 06:05:14.000
2647911          9209-09-25 00:00:00.000

Converting sas date into SQL server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.