Date fields in SQL queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am struggeling with date fields in queries to MS SQL200 server tables.
After having defined the recordset, I open the recordset with a query like
this:

SELECT <fields> FROM <table> WHERE <date-field-in-SQL-server-table> <=
<date-field-in-Access>

The system returns an arror because the date field in the SQL Server table
cannot be equated with the date field in Access.

Has anyone a solution for this?

/Leif S.
 
Nick,

The date field in the SQL server is of type smalldatetime and the server is
set opp to Norwegian date format which is "dd.mm.yyyy-hh.mm.ss"

/Leif S.
 
Hi Leif,

Try inserting the following code where the criteria is

iif(len(datepart("m", date)) = 1 , 0 & datepart("m",
date),datepart("m", date)) & "/" & iif(len(datepart("d", date)) = 1 , 0
& datepart("d", date),datepart("d", date)) & "/" & datepart ("yyyy",
date)

Hope that this helps,

Good luck,

Nick
 
Nick,

I have finally reached a solution or at least a work-around. I convert the
date field to a string formatted to yyyymmdd. Then I use this field in my
query.

Thanks for your help.

/Leif S.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date fields in SQL query 2
Referring to fields in a subquery 1
query on date 1
DATE HELP 1
Query milliseconds in a date field that is in a SQL Server table l 2
Formatting date gives bad results in query 2
Split data in field on character return 0
Joins on NULL Date values 3

Back
Top