Dealing with datetime and SQL Transact-SQL

I am trying to make a stored procedure in SQLServer Express.

The question is related to this stored procedure / transact - sql.

 I think i am doing something wrong with datetime.

Here is the stored procedure.

The error i am getting is that:

Msg 241, Level 16, State 1, Line 20
Syntax error converting datetime from character string.

 ===================================== 

DECLARE    @websiteID  int
DECLARE    @dateFrom  datetime
DECLARE    @dateTo  datetime
DECLARE    @sortbystring  varchar (20)

set @websiteID = 1
set @dateFrom = Convert(datetime, '2007-02-07 12:01:00')
set @dateTo  = Convert(datetime, '2007-03-07 11:59:00')
set @sortbystring = 'Campaign'

IF ISNULL(@dateTo, '') = ''
begin
    SET @dateTo = @dateFrom
end

SET @dateTo = DATEADD(d, 1, @dateTo)

DECLARE @str CHAR(400)

LINE 20: SET @str = 'SELECT dateEntry, c.name as Campaign, e.firstname as FirstName FROM entry e, campaign c WHERE e.campaignID = c.id ' + 'AND c.websiteID = @websiteID' + 'AND (ISNULL(' + @dateFrom + ', '''') = '''' OR e.dateEntry BETWEEN '' + @dateFrom + '' AND '' + @dateTo + '') ' + 'AND e.IP NOT IN (SELECT IP FROM IP) ' + ' ORDER BY dateEntry DESC'
print (@str)


=============================================== 


Target
0
rizwanaslamdar
4/5/2007 12:28:01 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

9 Replies
1031 Views

Similar Articles

[PageSpeed] 44
Get it on Google Play
Get it on Apple App Store

convert the @dateFrom and @dateTo to string in the SET @str = ... part

KH
0
khtan
4/5/2007 1:22:41 AM

NO IT'S NOT WORKING.

I have tried to convert the date in my string @str above to string and to execute the query.

The problem is that @str is a varchar type / string. \

I am building the string and then executing it at the end.

If i convert the date using

convert(datetime, @dateFrom)

i get the error while executing:

'syntax errror converting datetime from character string.

Any help would be appreciated.

 


Target
0
rizwanaslamdar
4/5/2007 2:01:27 AM

NO IT'S NOT WORKING.

I have tried to convert the date in my string @str above to string and to execute the query.

The problem is that @str is a varchar type / string. \

I am building the string and then executing it at the end.

If i convert the date using

convert(datetime, @dateFrom)

i get the error while executing:

'syntax errror converting datetime from character string.

In the database the date is of type datetime.

Any help would be appreciated.

 


Target
0
rizwanaslamdar
4/5/2007 2:02:24 AM

in your SET @sql statement

use  + convert(varchar(30), @dateFrom, 121) +

instead of + @dateFrom +
 


KH
0
khtan
4/5/2007 3:09:01 AM

are you using exec() or sp_executesql ?

 if you are using sp_executesql, you can just pass the @dateFrom, @dateTo in using parameters
 


KH
0
khtan
4/5/2007 3:11:05 AM

convert(datetime(object),Varicable,106)

may be this will work 


Nothing is really over,untill the moment stop trying for it...
Amitsp(MCTS,MCP)
sqlreporting.blogspot.com
0
prajapatiamit2003
4/5/2007 4:25:43 AM
the date variable contains time. should use style 121

KH
0
khtan
4/5/2007 4:40:27 AM

Just curous. Do you really required Dynamic SQL for the query ? Looking at the query you don't required to use dynamic SQL at all.

If for whatever reason you need to use Dynamic SQL which I would not advice to, you should use sp_executesql and not exec().

 


KH
0
khtan
4/5/2007 4:49:22 AM

Hi KH,

OKay.. due to the lack of my understanding of asp.net datagrid (which i want to have sorting) I am relying on this dynamic SQL.

What i am doing is retrieving data from the database and binding it to the datagrid where i have sorting allowed.

When i click on a column heading.. it should go back into the database and retrieve the data record from the database with an updated ORDER BY condition and databind the new retrieved data to show the sorted columns.

Is this the correct approach?

 

Below is my code from the asp.net file.

===============================

 

<%@ Page Language="VB" MasterPageFile="~/Reports.master" Title="Report - Entries" %>
<script runat=server language="VB">

    Dim SortFieldType As String = ""
  
   
    Private Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs)
        'Check if the user is authenticated
        If Session("authenticated") = "True" Then
           
        Else
            Response.Redirect("login.aspx")
        End If
           
       
        If Not IsPostBack Then
            If SortFieldType = "" Then
                SortFieldType = "IntegerValue"
            End If
            BindGrid()
        End If
       
       
       
    End Sub
   
    Sub BindGrid()
        Dim Entries As New Harvest.entry
        With Entries
            .dateEntry1 = Request.QueryString("SelectedDateFrom")
            .dateEntry2 = Request.QueryString("SelectedDateTo")
            .websiteID = Request.QueryString("WebsiteID")
            .sortByString = SortFieldType
   
            entrieslist.DataSource = .Entries()
            entrieslist.DataBind()
           
            DateFromLabel.Text = .dateEntry1
            DateToLabel.Text = .dateEntry2
            WebsiteIDLabel.Text = .websiteID
           
            DateFromLabel.Visible = True
            DateToLabel.Visible = True
            WebsiteIDLabel.Visible = True
        End With
    End Sub
  

   
    Sub SortField(ByVal sender As Object, ByVal e As DataGridSortCommandEventArgs)
        SortFieldType = e.SortExpression
        BindGrid()
    End Sub


   
    </script>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<form id="reportsform" runat="server">

            <h1>Report - Entries</h1>
            <b>Website ID:</b> <asp:Label ID="WebsiteIDLabel" Visible="False" runat="server"></asp:Label><br />
            <b>Date From:</b> <asp:Label ID="DateFromLabel" Visible="False" runat="server"></asp:Label><br />
            <b>Date To:</b> <asp:Label ID="DateToLabel" Visible="False" runat="server"></asp:Label><br />
           
            <asp:datagrid OnSortCommand="SortField" CellPadding="3" AllowSorting="true" id="entrieslist" runat="server">
            <HeaderStyle BackColor="#80BDDC" font-names="verdana" Font-Size="Medium" Font-Bold="true" />
                <AlternatingItemStyle BackColor="#B5CA89" Font-Names="verdana" Font-Size="Small" />
                <ItemStyle BackColor="#ffffff" font-names="verdana" Font-Size="Small" />
           
           
           
            </asp:datagrid>
           
            <p><a href="reports.aspx">Reports Main Page</a>
           
</form>
</asp:Content>

 

=============================================

 


Target
0
rizwanaslamdar
4/5/2007 1:47:09 PM
Reply:

Similar Artilces:

Watcom-SQL or Transact-SQL
In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures and triggers can be "viewed" as either Watcom or T-SQL. If I understand correctly, T-SQL is more transportable, as it is used by Sybase SQl Server, and also to some degree by MS SQL. Is Watcom SQL in place simply for backward compatibility, or are there other advantages? >In evaluating SQL Anyhwhere 5.5, I have noticed that stored procedures >and triggers can be "viewed" as either Watcom or T-SQL. If I >understand correctly, T-SQL is more transportable, as it is used by >Sybase S...

Dynamic SQL in Transact SQL
Does Dynamic SQL can be done in transact sql format in stored procedure? If yes, any reference can be read? Thx..... ...

SQL > My SQL
I have a problem, my asp.net page wants to connect to the sql server, but it has to be the mysql. How can I solve this problem? This is my code: <%@ page explicit="true" language="VB" debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script runat="server"> sub page_load(sender as object, e as eventargs) If Not IsPostBack Then dim connect as sqlconnectio...

SQL Express to SQL ?
I am developing a site using the express edition of all tools (VWD and SQL Express).  I want to use GoDaddy hosting and was wondering if I will be able to effectively put my SQL database and website up on their hosting even though it was developed via Express edition of Microsoft programs?...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <john.kingan@abibuildingdata.com> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

Transact-SQL issues with SQL Anywhere...
Hello, I am trying to write a stored procedure that I can use in SQL Anywhere AND in Sybase ASE. I am having compatability issues with Watcom-SQL versus Transact-SQL. I don't think the following Watcom-SQL procedure can be written in transact-SQL in such a way so that it can be used in both environments (ASA and ASE). WATCOM-SQL: alter procedure "DBA".sp_RebuildKeyPool_copy() begin declare nextid integer; set nextid=0; truncate table s_keypool; for eachdatabaseid as databaseidcursor dynamic scroll cursor for select database_id as currdatabaseid ...

SQL Exception Handling and SQL Transactions
Hi guys, Does any one know how to detect when a SQL transaction has been rolled back in either a windows application or ASP.NET. My Transactions always run but when they are rolled back Visual Basic does not pick up any errors in the 'Try Catch SqlException'. Does any one know a way round this. Sorry for the lack of code. Im writing this post on a friends PC but i wil put up my code as soon as possible. Thanks in advance Matt I guess you are having sql transaction(s) in some stored-procedures. In the stored-procedure you can use RAISERROR("Transaction failed", 16, 1) af...

Watcom-Sql vs. Transact-SQL
Hi Everyone, I'm in the process of writing some SP and.... 1) Which dialect should I concentrate on? 2) I can not figure out how to do functions in Transact-SQL Thank you for your help! "Jan K. van Dalen" <vandalen@csi.com> wrote: >Hi Everyone, > >I'm in the process of writing some SP and.... >1) Which dialect should I concentrate on? Unless you want to port to ASE, why do you care? Just write whatever works; personally, I mix the dialects :) If you want to port to ASE soon then stick to ASE. However, in the future ASE will suppor...

Transact-SQL vs. Watcom-SQL?
This falls under the "I know I'm supposed to know this but I don't" category: what is Transact-SQL? Is my understanding correct that Transact-SQL is the SQL dialect used by Microsoft's SQL Server, while Watcom-SQL is the dialect used by Sybase's SQLAnywhere? That's correct.. Sybase ASE and MS SQLServer came from the same codebase, many moons ago. The common dialect for both products is Transact-SQL, although they've diverged somewhat over the years. Watcom-SQL is the SQLAnywhere/ASA dialect. Paul Horan Rick Charnes <rick.charnes@state.ma...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

SQL: Run transaction on multiple sql servers
I have a stored procedure in which I am inserting record in multiple tables on multiple database SQL servers. At the start of the SP, I am starting Transaction and and the end commiting the transaction. Server running Stored Procedure is win 2003Second server is win 2000I am getting following error,The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::Joi...

Reference manual for watcom-sql and transact-sql
The on-line help for ASA 8 and ASA 9 have a few tidbits about watcom-sql and transact-sql... but there doesn't seem to be a language reference. At least not anywhere I can find one. Can anyone point me in the right direction? Regards, Mike Niemann For clarification, the T-SQL support in SQL Anywhere is for compatibility only. SQL Anywhere does not provide a complete implementation of the T-SQL language. For ASA9 (prior versions have equivalent documentation), see ASA SQL Reference SQL Statements Using the SQL statement reference Statement applicabili...

Combine WATCOM SQL and TRANSACT SQL Syntax
The WATCOM and TRANSACT SQL syntax descriptions should appear together in the Help. There are features of both which are helpful, and it is less than productive to have to read two Help topics to read about one SQL topic. For example, the syntax for SELECT is presented in two places, and they aren't connected in the "See Also" list. Also, the Help - Index facility should always jump to the syntax description as the first choice when a keyword like SELECT is chosen, not some general tutorial or other topic. Breck Hmmm. We'll have to see about this one. Here is...

Web resources about - Dealing with datetime and SQL Transact-SQL - asp.net.sql-datasource

Transact-SQL - Wikipedia, the free encyclopedia
Transact-SQL ( T-SQL ) is Microsoft 's and Sybase 's proprietary extension to SQL . SQL, the acronym for Structured Query Language, is a standardized ...

Resources last updated: 12/22/2015 3:27:56 AM