List All Minutes Between Two DATETIME Values

May 22, 2008

Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:

START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10

RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10

Thanks!

View 8 Replies


ADVERTISEMENT

Datetime To Minutes?

Aug 3, 2004

hey all, i need to find the ratio of difference in 2 datetime variables and the difference of another 2 datetime vars. I figured the best way to do it is to convert the difference in both numerator and denominator to number of minutes.

can anyone help ??

View 1 Replies View Related

How Can I Convert Datetime To Number Of Minutes

Oct 9, 2007

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

View 5 Replies View Related

Dealing W/ Minutes In DateTime Field

Apr 16, 2004

If I subtract 14 days from a datetime field, will the time of day that I run this query affect the resultset? I am running the query during "normal business hours", 8 am - 5 pm, and the records are entered during this time frame as well.

ddave

View 6 Replies View Related

T-SQL (SS2K8) :: Get Difference In Minutes Between 2 Datetime

Apr 26, 2014

I've table and data as follow,
declare @tAccount table
(
isLocked bit,
LastLockoutDate datetime,
currentDte dateTime
)

insert into @tAccount values('true','2014-04-26 16:11:25.337',getdate())
Let's say, current data as follow,
isLocked| LastLockoutDate| currentDte
12014-04-26 16:11:25.3372014-04-27 01:45:15.053

How to get different in minutes between currentDate and LastLockoutDate? Mean, currentDate - LastLockoutDate.

View 3 Replies View Related

Finding Minutes In Datetime Intervals [SQL Server2005]

May 29, 2008

[SQL Server 2005]


In a report there are following in parameters: from date, to date, from time and to time.

I need to match these search criteria€™s against start time and stop time in assignments.
The trick: How to write SQL that meets all different cases that can arise.

The query need's to traverse through every assignment,
and see if its start and stop time is in the interval given by the user.

It should then return the minutes from the assignment that is within the interval.



I'm thinking of packaging the minute "extracting" logic in a Scalar-valued function.

It should then take the in parameters from the user, and start time and stop time from the assignment as arguments.

To consider: Crossing midnight.



E.g. the user puts in

From date: 2008-05-24

From time: 22:00:00

To date: 2008-05-26

To time: 02:00:00



Since the user wants to evaluate a time span that crosses midnight,

I'll have to look in to time spans like:

2008-05-24 22:00:00 - 2008-05-24 23:59:59

2008-05-25 00:00:00 - 2008-05-25 02:00:00

2008-05-25 22:00:00 - 2008-05-25 23:59:59

2008-05-26 00:00:00 - 2008-05-26 02:00:00



Furthermore the start- and stop time can be partially in the above interval.

E.g. an assignment starts 2008-05-24 21:46:11 and stops 2008-05-24 22:36:05.

This would then yield from the time spans above 36 minutes.

The combinations seem never-ending€¦

View 9 Replies View Related

Abandon Hours, Minutes Etc From Datetime Datatype

Dec 13, 2007



Dear all,

I need to ignore the hours minutes and seconds elements of a datetime field - I've got a way of doing this in my select statement but it seems to be this can't be the most efficient way!

CONVERT(datetime, CONVERT(nchar(10), db.tab.field, 101)) AS date_key


Is there a better way?

View 4 Replies View Related

T-SQL (SS2K8) :: Adding Minutes (varchar) To Datetime Field

Mar 27, 2015

I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30

is there an easy way to add these together?

I don't mind if they get converted to integer as it will be used for comparison.

View 9 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server

Jul 9, 2007

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

View 3 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related

Transact SQL :: Getting Primary Key Values As A List Of Values

Oct 14, 2015

I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below

Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.

View 2 Replies View Related

T-SQL (SS2K8) :: Display Row As 2 Days Ago / 1 Hours 34 Minutes Ago / 11 Minutes Ago

Apr 21, 2015

My table as data as follow,

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))

[code].....

View 6 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

How To Select From A List Of Values

Jun 21, 2006

Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
 
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
 
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
 
How can I do that?
Thanks.jsn
 

View 6 Replies View Related

Dropdown List Values

Apr 28, 2007

Dear Readers of this post,
 
I have a dropdownlist that is populated with an sqldatasource as follows:
 
SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false'
 
The Datavalue vield of the DDL is populated with the [Title].
 
When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.
 
any ideas
 
Sat

View 3 Replies View Related

DropDown List Values

Nov 22, 2005

For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work?
<asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />

View 1 Replies View Related

List All ID Values In A Package

May 16, 2006

While experimenting I got this message:



[Flat File Source [212]] Error: The "output column "Column 0" (237)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 0" (237)" specifies failure on error. An error occurred on the specified object of the specified component"



I note under BIDS, Properties tab, that the Flat File is assigned an ID of 212. But is the number (237) and ID or an error value? How can I list all "ID's" used in a package?



TIA,

Barker

View 1 Replies View Related

SQL Statement - Query A List Of Values

Oct 31, 2006

Hello  I have a newbie question.  If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen 

View 4 Replies View Related

How To ORDER BY Designated List Of Key Values

Apr 20, 2006

I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.

So my query looks like:

SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????

I need the results returned in the order provided in the IN list (3,104,43,22,345).

Thanks in advance!

pr0

View 5 Replies View Related

List Of Values That Make SQL Blow

Sep 27, 2006

I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion

Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.

IE (ticks, #, :, -,)

I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.

I know this is a nightmare but it is the contraint I am working under.

View 14 Replies View Related

SELECT Statement From A List Of Values?

Jul 12, 2012

I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.

I tried using Case with no success.

For example:

Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'

So the 'where' line would produce one row, however, the overall statement would produce two.

ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB

View 4 Replies View Related

Returning A List Of Different Column Values

Sep 7, 2012

My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2

then run an sql statement and should return you the following
Period
1
2
4
12
31

View 3 Replies View Related

Can Store A List Of Values In A Variable?

Apr 16, 2014

I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.

I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.

View 7 Replies View Related

List Of Rows With Same Column Values

Nov 3, 2014

select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420

select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528

It seems there are some rows with same birthdate and operationdate.

I want to get the list of rows(with all columns) that has got same birthdate and operationdate

View 1 Replies View Related

Join To A List Of Values During Pass-through

Feb 3, 2015

We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.

I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server?

View 2 Replies View Related

Where Condition Filter List Of Values

Mar 24, 2015

SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,

[code]....

sb.CarrierId data is Sec.

Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.

I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.

View 1 Replies View Related

Pick List Values Into A Table

Jul 20, 2005

Can anybody help on this question?When I select 3 values from the drop down list box, how can I insert into atable?Please help

View 3 Replies View Related

Set A Attribute To Random Values In A Given List

Jul 20, 2005

Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing

View 1 Replies View Related

Transact SQL :: List All Available Values Of A Field?

Aug 10, 2015

I have [TableAccount.AccountType] field which can store these values:

Value
C
S
E

How do I list all available values for a field?  Using Distinct can only list all entries in the db, but does not list all values available for the field.

View 4 Replies View Related

Concatenate List Of Values Under A Group

Aug 4, 2007

I have the following dataset:

State ZIP Homes Schools
WA 98007 2000 4
WA 98052 3000 5
WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this.

My report needs to show:
State Homes Schools
[98007, 98052, 98079] 7000 12


Any help will be greatly appreciated

View 6 Replies View Related

List Of Values In Input Parameter

May 8, 2008



Hello,
I have the following issue:

I have the following statement into a function:
select a,b form T where c IN @parameter

t is the table
c is datatype= integer
@parameter is a input parameter in the function, the @parameter contains more values and passed as a string.

Running the statement above I got an error due to conversion type.

How can I pass a list of parameters in the @parameter variable to make the statement works?

Thank in advance.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved