Select date from a datetime column

Well yeah as the topic says, this is my sql question right now;

string date = "2006-11-12";
string sql = "SELECT * FROM spell_of_work WHERE date='{0}'",date);



problem is that my date column in the table is datetime and not a date so.. error!

 
i dont know if RLIKE or LIKE can help,

 wating for a reply!
herman
 

0
thrinker
12/16/2006 6:27:21 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

11 Replies
470 Views

Similar Articles

[PageSpeed] 31

SELECT * FROM spell_of_work WHERE date=' " & date & " ' "

I put spaces in between the quotes, so they could be seen better - you don't want to put those spaces there.


David Wier
MCP/ASPInsider
ASPNet101.com - where to look first!
Control Grouper - easily control properties for multiple controls with one control!
Calendar Express - The Best HTML Calendar Generator on the web!
(Please 'Mark as Answer' when it applies)
0
augustwind
12/16/2006 6:56:31 PM

string monday = "2006-12-19";

string sql = "SELECT * FROM spell_of_work WHERE date='" & monday & "'";

i get following error:

Error    1    Operator '&' cannot be applied to operands of type 'string' and 'string'

 

Anyone?
herman

 

0
thrinker
12/19/2006 8:08:50 PM

You are designating monday as a string - in your database, it's looking for a date -

try:

string datetime=........


David Wier
MCP/ASPInsider
ASPNet101.com - where to look first!
Control Grouper - easily control properties for multiple controls with one control!
Calendar Express - The Best HTML Calendar Generator on the web!
(Please 'Mark as Answer' when it applies)
0
augustwind
12/19/2006 9:09:55 PM

hmm, how do u mean?

 string monday = "2006-12-16";

 string sql Datetime = "SELECT id,started FROM spell_of_work WHERE started='" &monday& "'";

 

errors~
herman
 

0
thrinker
12/19/2006 9:26:06 PM
PLease use parameterized queries:
 
string sql Datetime = "SELECT id,started FROM spell_of_work WHERE started=@started;
Besides preventing SQL injection attacks, your code looks cleaner and you dont have to worry about where/how to put spaces/quotes etc.

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
12/19/2006 9:41:46 PM

well ok thanks but that didnt solve my problem

 

still, u cant do string sql Datetime = "";

wonder how he ment.

 

herman 

0
thrinker
12/19/2006 10:23:58 PM

This is how you would do it in VB:

dim sql as string="SELECT * FROM spell_of_work WHERE date>=CAST(floor(CAST(@date AS float)) as datetime) AND date<CAST(floor(CAST(@date AS float))+1 AS datetime)"

dim conn as new sqlconnection(ConfigurationManager.ConnectionStrings("ConnectString").ConnectString)

dim cmd as new sqlcommand(sql,conn)

cmd.parameters.add("@date",sqldbtype.datetime).value=now

...

 

 

0
Motley
12/19/2006 10:47:05 PM
bah dont work, working with c# hehe
0
thrinker
12/19/2006 11:06:17 PM

They are right - parameterized queries are the way to go - however, what I was talking about before was:

instead of:
string monday = "2006-12-16";
do
datetime monday  = "2006-12-16";

However, - here, note that since the system may see that as a specific DAY, I would recommend using something else - even adding an extra letter or two at the beginning, like:
datetime dtmonday  = "2006-12-16";

Then -
string sql = "SELECT * FROM spell_of_work WHERE date='" & dtmonday & "'";

 Do check out parameterized queries - I know I have a 2 part tutorial on it, though the core of the example code is VB (http://aspnet101.com/aspnet101/tutorials.aspx?id=1). This will tell you most of what you need to know about what it does, how to use it and why it's better.


David Wier
MCP/ASPInsider
ASPNet101.com - where to look first!
Control Grouper - easily control properties for multiple controls with one control!
Calendar Express - The Best HTML Calendar Generator on the web!
(Please 'Mark as Answer' when it applies)
0
augustwind
12/19/2006 11:36:46 PM

Well im having a function that get the first day of the week looks like this:

 

        int iWeek = week;
        int iYear = year;

        DateTime dtDate = DateTime.Parse("01/01/" + year.ToString());
       
        dtDate = dtDate.AddDays(7 * iWeek);

        //ADD DAY UNTILL FIRST MONDAY IS FOUND
        while (dtDate.DayOfWeek != DayOfWeek.Monday)
        {
            dtDate = dtDate.AddDays(1);
        }

        string strMonday = dtDate.ToString("yyyy/MM/dd");

        DateTime dtMonday = Convert.ToDateTime(strMonday);

        string sql = "SELECT * FROM spell_of_work WHERE date='" & dtMonday & "'";

 

that should help u guys more,
herman 

0
thrinker
12/20/2006 12:17:03 AM
and up we go!
0
thrinker
12/20/2006 7:57:02 PM
Reply:

Similar Artilces:

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

Parse DateTime into Date or select DateTime as Date from Access
I am getting DateTime values out of an Access Database and I want to only use the Date part of this field. I have tried to cast the returned value to a DateTime value and then format the DateTime to only show the Date part in the toString() fcn. <asp:Label runat="server" Text='<%# getDate(DataBinder.Eval(Container, "DataItem.ACTIVE_FROM"))%>'> But I can't cast the object to a DateTime object. I have also tried using DateTime.Parse() but this gives an error saying that the format is not correct.         {       ...

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? Can you post the SQL you have currently and how the value from the ftont-end is passed to the DB?***********************Dinakar NethiLife is short. Enjoy it.*********************** <asp:SqlDataSource ID="LDSSqlDataSource1" runat="serve...

how to show only date, no time, from a sql table datetime column?
I call Fill() to fill a datatable from a sql select statement. One data column is datetime object. da = new SqlDataAdapter("select OrderID, OrderDate from OrderTable", connectionstring); Here, OrderDate column is of SmallDateTime type in the DB When I bind the datatable to a datagrid's BoundColumn, it show the full date time string on the cells of this column. Is it possible to show only date part? Thanks. Using the boundcolumn there is an attribute that you can use: DataFormatString. Take a look at this example code. Grz, Kris.Read my blog. Handy Firefox plu...

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

Query SQL DateTime column using ADO.NET Entities Framework
I have a SQL table called Slots with a Datetime column named Date.  I would like to query the db and return all rows where the date is equal to a date provided by the user on an asp.net form.  I have tried various ways to covert the datatime column, but always get an exception.  Any suggestions how to query a SQL datetime field where the date column is equal to a date specified by the user?    using (DBEntities1 myDB  new DBEntities1())   {      try        {  string querySt...

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

Getting just the date from a "datetime" sql column along with other data of different formats
Hi. I have a web page that gets the current user, then goes into a database and gets a list of invoices, this includes: invoice numbers, the date of the invoice, the date the invoice was paid, and the amount of the invoice. The data is then bound to a repeater control and displayed. i need a way to get just the date and not the date and time from the datetime columns. Here is my code that i have now: This next part gets the current user, then creates the selection string and binds the repeater to the data.Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) ...

GridView Selection , want use column data, data is DateTime, Accessdatabase , want compare with real time date
i using access database. i have a gridview and when select the gridview, a message box will pop out if the date of today is lesser then the date in the data that is selected. Hi probotz, You can place a LinkButton into GridView as select button and handle its Click event to show popup message: <asp:TemplateField>            <ItemTemplate>              <asp:LinkButton ID="LB_select" Text="Select" CommandArgument='<%# Eval(&q...

Convert .NET DateTime format to SQL DateTime format
Hi!!I'd like to know if there is a function to convert the DateTime format used in C# (VS2005) : dd/mm/yyyy HH:mm:ss to the format used in SQL Databases: yyyymmdd HH:mm:ssThanks folks!...

Calendar Select does not select a date if datetime value contains a time other than midnight
If you set a Calendar control SelectedDate with a DateTime where the there is a date part, and a time of 00:00:00, the calendar correctly selects that dateIf the time part is not midnight, the Calendar does not select the date.I used the DateTime.Date value to set the date from a DateTime value with non-midnight datesIs this a bug? or by quirky designIf it was easy, everybody would be doing it. I could be wrong but more like quicky design because past midnight is in the 24hour clock which is in TimeSpan.http://msdn2.microsoft.com/en-us/library/system.timespan.aspxKind regards,Gift Peddie ...

Reuse derived columns in same SQL Select statement under SQL Server 2005
Hello, Our agency is in the process of migrating our Sybase Adaptive Server Anywhere 7.0 databases to Microsoft SQL Server 2005. We are currently using PowerBuilder 10.5. We have several datawindows that reuse SQL computed fields (derived columns) in the same SQL Select statement. Here is an example: select institution_code, incident_number, sequence, final_review_date, ts_status_code, idoc_number, (CASE WHEN coalesce(s.recommended_verbal,'N') <> coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal, (CASE WHEN coalesce(s.recomme...

C# DateTime and SQL Server "DateTime" column type
If I have a property in a class that will be set to a date returned from a SQL Server DateTime column... Should my property be of type "string" or type "DateTime". Hi Son Volt,You would have to use DateTime type in your class.Good Coding!Javier Luna http://guydotnetxmlwebservices.blogspot.com/from Villa El Salvador for world!...

SQL SELECT by a date
I am trying to create a SELECT statement using a date. No data returns. However if I use the same Select Statement using ID or any other column it works fine. Is there a trick to using a date in a select statement? Ex: ("Select * From Events WHERE Event_Date Like '%" & 12/21/2004 & "%'" , DBConn) Have you tried not treating your date field like a string, but to actually use it like a date?Thomas TomiczekPowerNodes ApS(Microsoft MVP C#/.NET)---Building a Website? Try the PowerNodes CMS - http://www.powernodes.com/ Is your Event_Date column a datetime o...

Web resources about - Select date from a datetime column - 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: 11/26/2015 10:55:59 PM