Convert Integer to Date in Access ADP Project

[johncnj]

I am new to creating Access projects with SQL databases. I have created a
View from two tables in my database. One of the tables contains a field that
stores the date as an integer. I need to convert the field into a date. In
Access MDB, I would create a formula using a DateValue function but in Access
ADP I get a message the DATEVALUE function is not recognized.

 

[Sylvain Lafontaine]

This question would be best asked in a newsgroup specific about the T-SQL
language such as m.p.sqlserver.programming. Also, the official newsgroup
for ADP is m.p.access.adp.sqlserver.

As for your question, use the Convert or the Cast function:

select convert (datetime, 0)

Don't forget that there is a difference of two days between Access and
SQL-Server for an integer value: CDate (0) = '1899-12-30' in Access while
Convert(datetime, 0) will give you '1900-01-01'.

 

[johncnj]

That worked, thank you!

This question would be best asked in a newsgroup specific about the T-SQL
language such as m.p.sqlserver.programming. Also, the official newsgroup
for ADP is m.p.access.adp.sqlserver.

As for your question, use the Convert or the Cast function:

select convert (datetime, 0)

Don't forget that there is a difference of two days between Access and
SQL-Server for an integer value: CDate (0) = '1899-12-30' in Access while
Convert(datetime, 0) will give you '1900-01-01'.