Convert String to Datetime in SQL

[lhtan123]

I have a form with two combo boxes and one textbox which allows user to enter
a date. If this criteria is used to filter records from a query by an sql
string, it runs into error.

I guess it's becos' Date is a string value while the table design has it in
a Date/Time format.

I've tried to look through some examples online but couldn't find a suitable
one to solve my problem. My SQL statement is something like below:

If Not IsNull(Me.txtDate) Then
strWhere = strWhere & "([Date] = " & CDate(Me.txtDate) & ") AND "
End If

 

[Tom van Stiphout]

On Mon, 12 Jan 2009 00:48:01 -0800, lhtan123

Since you posted in this NG I am assuming your Back-End is SQL Server.
It requires date values (and text values) to be wrapped in
single-quotes.

-Tom.
Microsoft Access MVP

 

[Paul Shapiro]

CDate is an Access function, and doesn't exist in SQL Server. You want to
build a string. The query you want might be:
Where [TheDateField] = '2008.01.12'

SQL Server always interprets dates correctly if given yyyy.mm.dd and other
than that I think it usually accepts American date format, mm/dd/yyyy, even
if the language is set to something other than US. You can find details in
SQL Books Online. I just make a habit of using the yyyy.mm.dd format to
avoid any language/region issues.

If you're using Date as a field name, you should change it now if you can
since it's a reserved word in most SQL dialects.

On Mon, 12 Jan 2009 00:48:01 -0800, lhtan123

Since you posted in this NG I am assuming your Back-End is SQL Server.
It requires date values (and text values) to be wrapped in
single-quotes.

-Tom.
Microsoft Access MVP

I have a form with two combo boxes and one textbox which allows user to
enter
a date. If this criteria is used to filter records from a query by an sql
string, it runs into error.

I guess it's becos' Date is a string value while the table design has it
in
a Date/Time format.

I've tried to look through some examples online but couldn't find a
suitable
one to solve my problem. My SQL statement is something like below:

If Not IsNull(Me.txtDate) Then
strWhere = strWhere & "([Date] = " & CDate(Me.txtDate) & ") AND "
End If

 

[lhtan123]

It's not connected to SQL Server but a shared drive. Sorry to have posted my
question here. I'm using VBA to construct the SQL string.

So should post it to "DAO VBA" ?

CDate is an Access function, and doesn't exist in SQL Server. You want to
build a string. The query you want might be:
Where [TheDateField] = '2008.01.12'

SQL Server always interprets dates correctly if given yyyy.mm.dd and other
than that I think it usually accepts American date format, mm/dd/yyyy, even
if the language is set to something other than US. You can find details in
SQL Books Online. I just make a habit of using the yyyy.mm.dd format to
avoid any language/region issues.

If you're using Date as a field name, you should change it now if you can
since it's a reserved word in most SQL dialects.

On Mon, 12 Jan 2009 00:48:01 -0800, lhtan123

Since you posted in this NG I am assuming your Back-End is SQL Server.
It requires date values (and text values) to be wrapped in
single-quotes.

-Tom.
Microsoft Access MVP

I have a form with two combo boxes and one textbox which allows user to
enter
a date. If this criteria is used to filter records from a query by an sql
string, it runs into error.

I guess it's becos' Date is a string value while the table design has it
in
a Date/Time format.

I've tried to look through some examples online but couldn't find a
suitable
one to solve my problem. My SQL statement is something like below:

If Not IsNull(Me.txtDate) Then
strWhere = strWhere & "([Date] = " & CDate(Me.txtDate) & ") AND "
End If

 

[Tom van Stiphout]

On Mon, 12 Jan 2009 18:00:04 -0800, lhtan123

That would be better, but I think between Paul and myself you should
already have the solution.

-Tom.
Microsoft Access MVP

It's not connected to SQL Server but a shared drive. Sorry to have posted my
question here. I'm using VBA to construct the SQL string.

So should post it to "DAO VBA" ?

CDate is an Access function, and doesn't exist in SQL Server. You want to
build a string. The query you want might be:
Where [TheDateField] = '2008.01.12'

SQL Server always interprets dates correctly if given yyyy.mm.dd and other
than that I think it usually accepts American date format, mm/dd/yyyy, even
if the language is set to something other than US. You can find details in
SQL Books Online. I just make a habit of using the yyyy.mm.dd format to
avoid any language/region issues.

If you're using Date as a field name, you should change it now if you can
since it's a reserved word in most SQL dialects.

On Mon, 12 Jan 2009 00:48:01 -0800, lhtan123

Since you posted in this NG I am assuming your Back-End is SQL Server.
It requires date values (and text values) to be wrapped in
single-quotes.

-Tom.
Microsoft Access MVP


I have a form with two combo boxes and one textbox which allows user to
enter
a date. If this criteria is used to filter records from a query by an sql
string, it runs into error.

I guess it's becos' Date is a string value while the table design has it
in
a Date/Time format.

I've tried to look through some examples online but couldn't find a
suitable
one to solve my problem. My SQL statement is something like below:

If Not IsNull(Me.txtDate) Then
strWhere = strWhere & "([Date] = " & CDate(Me.txtDate) & ") AND "
End If

 

[raibeart]

since you are using an ADP, you should be using a stored procedure
that will take all the parameters you are developing and then
displaying the data based on it. There is no DAO.QueryDef in an ADP
project. All of the queries are SQL Server views.

If you are saving the entire WHERE clause in this table for use later,
then you could do a stored procedure something like this:

CREATE PROCEDURE dbo.pQueryName
@WhereClause VARCHAR(1000),
@OrderByClause VARCHAR(500)
AS
BEGIN
DECLARE @Sql NVARCHAR(4000)
SET @Sql = 'SELECT * FROM tblNames ' + @WhereClause + @OrderByClause

EXEC sp_executesql @Sql
END

If you implement this, you should also be checking for a ; in either
the where clause or the order by clause and default them to an empty
string if either one is found so you do not get an injection attack.

Robert

On Fri, Jan 16, 2009 at 2:17 AM, microsoft.public.access.adp.sqlserver

== 1 of 2 ==
Date: Thurs, Jan 15 2009 10:08 am
From: Guy Kerr


This sounded like something that wouldn't be too challenging but for
some
reason I can't figure out the best way to do this.

I have an unbound form that contains Listboxes, radio buttons, unbound
controls, checkboxes etc. that correspond to fields in my database.
This
form is designed to build a query - or at least the WHERE CLAUSE.
Once the
user checks the boxes, fills in the unbound controls they click a
button to
generate a query. For example if the user fills in Kerr* in LastName
field,
checks off the >18 checkbox, select 'CA' from the State listbox then
clicks
the GENERATE QUERY button a record is created in a table that is
essentially
the Select Statement based on what the user selected on the form. So
the
text generated would be "Lastname like '%Kerr' and Age > 18 and State
= 'CA'".

Then when a user opens a form it does a DLookup based on the user name
to
search the table for the latest query to correspond to that user. It
then
uses this to apply the ServerFilter to the form which limits the
dataset to
what the defined query is. This is not relevant to what I'm trying to
accomplish, I just wanted to explain the function of the form and the
table
storing the queries.

What I WANT to do is export data to TAB Delim or CSV from the same
table
based on any of the queries stored in this table. I was looking at
DoCmd.TransferText but it looked to me that you had to refer to a
named
query. I was also considering using a report then export from that.
This
way the user can see the data before choosing to Export.

If anyone has any ideas on how best to do this I would be very much
appreciative.

Guy




== 2 of 2 ==
Date: Thurs, Jan 15 2009 10:33 am
From: "Paul Shapiro"


You can use a named query and just update its sql before exporting.
Open a
DAO.QueryDef, and then I believe the sql is one of the properties. Set
it to
your new sql string, save the querydef, and then export it.

 

[raibeart]

Sorry guys I posted this to the wrong thread.