Select all dates from SQL datetime field

I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?

0
frist44
8/3/2007 2:50:30 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
643 Views

Similar Articles

[PageSpeed] 49

Can you post the SQL you have currently and how the value from the ftont-end is passed to the DB?


***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
8/3/2007 3:33:12 PM

<asp:SqlDataSource ID="LDSSqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TimeAccountingConnectionString %>"

SelectCommand="SELECT * FROM [tasks] WHERE (([firstname] LIKE '%' + @firstname + '%') AND ([lastname] LIKE '%' + @lastname + '%') AND ([office] LIKE '%' + @office + '%') AND ([project] LIKE '%' + @project + '%') AND ([team] LIKE '%' + @team + '%') AND ([dateworked] = @dateworked))">

<SelectParameters>

<asp:ControlParameter ControlID="txtFirstName" DefaultValue="%" Name="firstname" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="txtLastName" DefaultValue="%" Name="lastname" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="dwnOffice" DefaultValue="%" Name="office" PropertyName="SelectedValue" Type="String" />

<asp:ControlParameter ControlID="dwnProject" DefaultValue="%" Name="project" PropertyName="SelectedValue" Type="String" />

<asp:ControlParameter ControlID="dwnTeams" DefaultValue="%" Name="team" PropertyName="SelectedValue" Type="String" />

<asp:ControlParameter ControlID="dwnDateWorked" Name="dateworked" PropertyName="SelectedValue" DefaultValue="%" Type="DateTime" />

</SelectParameters>

The drop down box is connected to a sqldatasource that finds only the unique dates in the table. I want to add an item to the top and have it be for "All dates..."

 

0
frist44
8/3/2007 3:42:43 PM

We could do like this but I would be concerned about query plans, or is that not a worry?

 

SELECT * 
FROM [tasks] 
WHERE (
		([firstname] LIKE '%' + @firstname + '%') 
		AND ([lastname] LIKE '%' + @lastname + '%') 
		AND ([office] LIKE '%' + @office + '%') 
		AND ([project] LIKE '%' + @project + '%') 
		AND ([team] LIKE '%' + @team + '%') 
		AND ([dateworked] = CASE WHEN @dateworked = 'All Dates' Then [dateworked] ELSE @dateworked END)
		)
 
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
8/3/2007 4:13:19 PM
Reply:

Similar Artilces:

Sql SELECT returning NULL on DateTime field
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where  Date1='8/9/2006 11:17:41 AM' 2)select * from X where  Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance That means no qualified record for #2 query. Are you sure...

How can I update an MS SQL 7.0 datetime field with embedded sql
I have a datetime field in MS Sql 7.0 named Revised_Date. I can update this date with my datawindow but I can't update it with embedded sql. I have tried the following which doesn't work UPDATE dbo.employees SET revised_date = date() ; produces invalid function error UPDATE dbo.employees SET revised_date = 2000-07-31 ; which yeilds 05/17/05 which is incorrect date ld_date time ldt_datetime ld_date = today ldt_date = datetime(ld_date) UPDATE dbo.employees SET revised_date = :ldt_date; which yields error How can I accomplish this ? T...

HOW DO I: Insert nulls into SQL dateTime field stored in SQL DB from a web UI textbox
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.    Option 1:     Presently implemented:  Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception   ParamValue    {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime}          ...

Incorrect selection of the actual date using Embedded SQL in MS SQL / OLE-DB
we're migrating our application from PB7 to PB10 ( using MS SQL Server ), and i'm having a problem when selecting the date and time from SQLServer; In embedded SQL, the result-set doesn't show the time of actual date... Example: At 15/02/2005 16:15:20 executing the instrucion below, datetime ldt_now select getDate() into :ldt_now from tb_x; It gets 15/02/2005 00:00:00 in ldt_now... PS: The object transaction is configured as soon: // Profile x SQLCA.DBMS = "OLE DB" SQLCA.LogPass = "gsdf" SQLCA.LogId = "gfsd" SQLC...

Select three fields in SQL datasource but only display two in datalist
How would I do this?  I have to select the First and last name to show up in the datalist, but I also need to select the key because I am updating another column in the selected row.  How would I display only the first and last name? You can edit the html in the aspx page and remove that item from the templates.BrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. The problem is the template doesnt know the name yet because the sql select is selecting from a "' + table + '" so that wouldnt work.  Sorry I forgo...

A SQL-TRANSACT Question How to Return only the Date Part of a DateTime Field.
VWD 2008 Express. Visual Basic. I have a SQL table I am querying from my aspx code behind.  The table has a column that is a datetime datatype.  I want to only return the date portion of the column.  What can I use in my SQL SELECT statement to return only the date part?  By this I mean instead of mm/dd/yyyy hh:mm:ss I just want mm/dd/yy.  I have seen the CONVERT function, but it does not seem to do the job.  Thanks for any help.Dr. Douglas PruiettGood News Jail & Prison Ministrywww.goodnewsjail.org select convert(varchar, getdate(), 101) - WilliamPl...

linking a sql field to point to the value of another sql field
is it possible to create hyperlinks in powerbuilder such that if you retrieve two fields from a database.. and want to display the first field on the screen and have it link to the value of the other field (when clicked) which is actually a URL address, you can? any help will be appreciated. thank you! ...

Comparing to DateTimes in SQL-Select-Statement when one Date can be null
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null))  @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!  Try this:  [End] IS NULLLimno...

.NET DateTime and SQL Server DateTime problem
.NET DateTime and SQL Server DateTime problem  I have the following if statement in an SQL stored procedure: IF (@Image = 1 AND @DateTimeUploaded = (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))   The value of the @DateTimeUpdated variable was taken from the ImageMainDateTime field when the data was requested by a ASP.NET webpage and is passed back here when that page is submitted so I can compare what the DateTime was when the data was requested with what it is now, in other words I’m doing a concurrency check (I can’t use TimeStamp for many reasons, ...

attempting to retrieve datetime field using sql select statement in ms db
Please note that the date type defined in the db is datetime. this will work without querying for the date - [Reservation].[ResvNumber] LIKE 'R%, but would not work when i include - AND [Customer].[Arrival] ='#" & strDate & "#'  " & _"OR [Customer].[Arrival] >'#" & strDate & "#'"how can i fix this, thanks *******************************************Function MyQueryMethod() As System.Data.DataSet Dim strDate As String strDate = CurrTime.Now.ToShortDateString strDate = strDate.ToString 'Dim StDate As Date = strDate 'Dim SDate As DateTime strConnection = "Pro...

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"> ...

Calendar Control
Happy Friday Everyone:  Quick question for everyone.  I have a page developed with a couple of fields that I'm taking input off of and then putting that input into my SQL table when the user clicks the submit button.  One of the controls I have in place is the calendar control.  What I want to be able to do is just have my user select a date off of the calendar control and then when they click the button, on that action the Selected Date get transfered into my insert statement via the SQLCommand.Parameters.Add("@date",etc....)  ---- The type of da...

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 20Syntax error converting datetime from character string.  ===================================== DECLARE    @websiteID  intDECLARE    @dateFrom  datetimeDECLARE    @dateTo  datetimeDECLARE    @sortbystring  varchar (20)set @websiteID...

How to save a picture in a SQL image field and how to show a picture from a SQL image field
HI! I am searching now a real long time in the forum and I found a lot of stuff to this topic. But please help me it doesn't work! On my webapplication there is a possibility to save a private picture.If a new user is saved in the database I want to save a fixed picture as startpicture.Dim picturePath As String = Request.ServerVariables("APPL_PHYSICAL_PATH") & "img\anonymous.jpg" Please tell me the way how to save this anonymous.jpg from the picturePath into the database and after that how to display the picture. Thank you! .         ...

Web resources about - Select all dates from SQL datetime field - asp.net.sql-datasource

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 12/11/2015 4:08:50 AM