.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Finding records using time datatype

Posted By:      Posted Date: October 10, 2010    Points: 0   Category :Sql Server

I need to find rows in a table that have StartTime and EndTime that fall between sent start and end time parameters.  Below is the design of the table I want to search and below it is an example of a SELECT statement.  I need to add to the SELECT another condition (e.g. AND StartTime BETWEEN @StartTime AND @EndTime OR EndTime BETWEEN @StartTime AND @EndTime).  Any help is appreciated.

CREATE TABLE [dbo].[CareScheduleDetail](
 [CareScheduleDetailID] [int] IDENTITY(1,1) NOT NULL,
 [CareScheduleID] [int] NOT NULL,
 [WorkDay] [tinyint] NOT NULL,
 [StartTime] [time](7) NULL,
 [EndTime] [time](7) NULL,
 [SvcType] [varchar](4) NULL,
 [Effective] [date] NULL,
 [Ended] [date] NULL)


SELECT     WorkDay, StartTime, EndTime
FROM         dbo.CareScheduleDetail
WHERE     (Ended IS NULL)

View Complete Post

More Related Resource Links

What can be the datatype for a column having records as weblinks?

Hi, i am trying to insert records in a column having records as weblinks but it gives me following error. --------------------- Msg 128, Level 15, State 1, Line 2 The name "www.yahoo.com" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. -------------------------------- NOTE : i have tried nvarchar(2083) but still giving me the same error. but if i try insert something like http://yahoo.com i get ERROR : incorrect syntax near 'http:'-   

Select random 10 records in sql server 2005 in terms of time

Hello,i have a query that select random records in access database in terms of time : "SELECT TOP 10 * FROM Tbl ORDER BY Rnd(-10000000*TimeValue(Now())*[ID])"now i wish to convert this query for using in sql server 2005 such that return random records in term of time 

Updating bunch of records at time

Hi, If I want to update 1000 records at a time in the database what is the best approach.Guide me Pls.

How to assign datatype at run time to a declared var in a stored proc?

Hi, I want to declare a local var. in sp. such that it would have the same datatype as a column in a specific table Example (forgive the syntax!) Decalre @Address  CustomerTable.Address The above would translate to the following at execution time Declare @Address VARCHAR(200) This feature was in Oracle, is it in SQL Server as well? Thanks. EK

Dynamically Finding the Datatype of a value


I suspect that it would probably be quite fraught with issues but is there a quick and not too intensive way to find the datatype of a column in a table. ISNUMERIC() can do this for a subset of the values, but there aren't equivalents such as ISUNIQUEIDENTIFIER or ISDATETIME as far as i know.

DBA, Datacruncher and Boardgamer

SQLColumns and SQLColAttributes return different attribute for TIME datatype using SQLNCLI10 driver


Hello everyone,

I am using SQL Server 2008 native driver. When I query the attributes of a TIME column in SQL Server 2008, I see that using SQLColumns() API, I get length as 10 whereas with SQLColAttributes() I get the length as 12.

My application stores the column attributes got from SQLColums() call and use that length during a SQLBindCol(). Since SQLColumns() give me 10 as length, when I bind a buffer of 10 bytes, I get a "numeric overflow" error.

Please let me know if there is some setting that I might do so that attributes returned from SQLColums() and SQLColAttributes() are same.



Updating Multiple records at 1 time.


You got to love management, they can never make their minds up on what they want. 

I'm currently working on a asp.net project using a datagridview that acts for the most part like a spreadsheet.  What I have to do now is update all the values in the table from the selected date, hour, min, max and cost forward.  There are 7 days worth of data in the table, when the user alters either the min, max or cost I need all the values in that field from that point onward to have the new altered value.

Here's an example:

In hour 10 of the day the user want to increase the max from 5 to 19, the min stays the same and so does the cost.  Mind you any or all of these values could change.  The only once that will remain as-is is the Date and Hour field:


Date Hour

SELECT Random Records in term of time



I want to return random records in term of time whithout NEWID

Insert on button event is inserting 2 records at a time


I have a form that is doing a simple insert, then a sub insert of a certain drop down selection is made. What i dont get is why the main insert is inserting duplicates and the sub works fine inserting 1 record per text as expected. I click the button 1 time, and when i check the table, there are 2 new records.. As you can see the insert is not wrapped with any conditions, once that is inserted i check if a slection was made and runs the sub insert.

My full code can be found in this post(http://forums.asp.net/t/1608619.aspx), but here is what i have for my button event.

    protected void btnSaveServConfig_Click(object sender, EventArgs e)   
        dac.insertServerConfigintosql(txtEmailLogin, txtEmailPass, Convert.ToInt32(ddlNumEmail.SelectedValue));   
            if (Convert.ToInt32(ddlNumEmail.SelectedValue) > 0)   
                 Dictionary<string, string> camValues = new Dictionary<string, string>();   
                    foreach (HtmlTableRow row in table1.Rows)   
                        foreach (HtmlTableCell cell in row.Cells)   
                            foreach (Control control in cell.Controls)   

want to display 24 hours time and date with datatype Datetime (Not String) in SSRS


Hi All,

I want to display 24 hours time and date in SSRS & for that I am using Format(now,"MM/dd/yyyy HH:mm:ss") but I am using this expression in Parameter, where I need to define datatype of that parameter as DateTime, not string. For Expression Format(now,"MM/dd/yyyy HH:mm:ss"), If I use DateTime datatype, It is showing error but working with String datatype.

For example:

I have parameter To Date  and value of this parameter should be 10/11/2010 13:09:16 (To Date: 10/11/2010 23:09:16) , not in AP or PM format and datatype of this parameter, want to keep DateTime that I can select value from Calender.

Please suggest me how I can achieve this?. I don't want to do it in stored Procedure side, want to do it in report side.

Thanks Shiven:)

Finding Records that were not deleted after update


I am trying to find a way to show the records that were not udpated after we run a Tscript that contains a large amount of update statements.  I dont know why the records are not being updated and is irrelevant.  We just need to see what records were NOT affected and I have been looking for 4 days and cant find anything.  The only thing that I found that was close was the OUTPUT statement, but that only allows us to see what WAS updated and there are so many records that taking the time to put it into Excel will be our very last resort!!

Someone please help me before I go out of my mind :(





Time Dimension for YTD using assigned Month and Year integers without Date datatype


Hi, most time dimensions are setup using a base Date field in the fact table, and they have plenty of issues for time analysis as it is. However my fact sales records have the time aspect assigned by pre-calculated periods, because depending on various factors, monthly final invoices are all raised on varying days (usually 2nd friday of month but can change). The monthly period is therefore not a straight calendar month. Probably a very common scenario.

So, the invoicing system already assigns the year (ie 2009, 2010, 2011) and monthly period (1, 2, 3 ... 12 with 3 representing march, even though that might represent 13th march to 9th april) and I want to use those as Time dim so we can do YTD, growth-on-prev-year etc.

It looks like its best to setup 2 Dimensions to link to 2 DataColumns/Attributes in the Fact table (say, FYear and FMonth, both integers). That way I can assigned attribute names like March to key column 3. If I combined them into 1 dimension with both fields making up a single key column, would have to either repeat the month names or link it to another Star schema I believe.

I can use the Add Business Intelligence wizard to make the Dimensions into Time ones instead of regular but I'm still not totally sure if this is the best structure/method and once done, how to use the YTD calcs to show in the cube browser (and my MDX knowle

Need to query three records at a time.


I need to write a select that pulls all of the rows from a data table but the problem is that I need to get the rows three at a time.

I am querying SharePoint data and since SharePoint only allots so many fields per record per data type, my list has three rows for every list item. So in order to sum fields 1-31 of loat data I actually need to grab row instance 0, 1, and 2.

For each list item field float 1-12 holds 1-12 in row instance = 0 and float 1-12 holds 13-24 in row instance = 1 and float 1-7 holds float data for 25-31 in row instance = 2. The field name of the row instance is . I need to sum fields float1-float12 for the three rows and get one total.

Normally I would use something that summed the 12 float fields and assign it to one variable field to be returned in the select. But I have to loop through the three rows to get that for each and then sum the subtotals of the three rows adn repeat until eof.

Can anyone give me a pseudo code for the select? I am just not that fancy with SQL I guess.

You can assume that there are always exactly 3 rows for each item whether or not there is data in any of the given fields. If the field has no positive data it is recorded as NULL in the field.

Thank  you in advance to any with ideas.


Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

ASP.NET GridView - edit records using JQuery Dialog

There are many ways in which you could modify modular data in Grid View:
1. Inline by making rows editable when u click on Edit
2. By using separate pages for Edit
3. By using popups/dialogs for adding/editing

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend