SQL Equivalent statement to DateValue() in Sharepoint.

Question

I am needing a cross platform way to do date inequalities with dates in their integer form. I have settled with the output of what DateValue() provides in Sharepoint. SQL is the only application I have yet to determine how to convert a conventional date format "2022-07-02" to this integer. In Sharepoint, DateValue("2022-11-02") gives 44603. How do I get this in SQL?

Answer 1

To obtain 44603, check an example:

set dateformat ydm
declare @date as datetime = '2022-11-02'
select datediff(day, cast('1899-30-12' as datetime), @date) -- returns 44603

Answer 2

@Viorel

Yes! This is exactly what I was needing. Perfect!

Well done!

Answer 3

@Viorel

This actually does work, however, the column it creates does not define the variable type. It is blank, and wont save because of it. I also cannot go into Deisgn and give it a variable type. It is greyed out and cannot be modified. How can I give the column an explicit int type in this method?

I would create the column first and then use Update table.... but, that doesnt work for other unknown reasons. I would like just to get this to work.

Answer 4

Did you try to add cast(datediff(...) as int) as IntegerDate ?

Answer 5

@Viorel , @Anonymous

I guess I never did say what I was doing with it! I'm adding a column, and making an existing date into integer.

alter table OrderData_SN  
add OrderDataInt  as datediff(day, cast('1899-30-12' as datetime), cast(OrderDate as datetime))  

I do this, it makes the column without errors, but doesnt give it a data type.

I could make the column first, but then its like datediff() is different in the context of an update. No idea what it wants.

alter table OrderData_SN  
add OrderDate INT  

That works fine, but this doesnt:

update OrderData_SN  
set OrderDateInt = datediff(day, cast('1899-30-12' as datetime), cast(OrderDate as datetime))  

OrderData is in date format, hence the change to datetime.

It tells me:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

where is the varchar data type???

Maybe I'm wrong here, @Viorel , but when I pull out just the date text conversion, perhaps this is where the problem is. Your statement provides the proper logic, but how do you get that text in a datetime for the datediff()?