Get Maximum From Each Datetime Values

Mar 5, 2008

hello all,

i have table - compmail in that i have compmailid, compmaildetail and compmaildate - columns

compmailid compmaildetail compmaildate
-------------------------------------------
94341 comp mailing list 2008-1-23 16:41:17.037
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108229 comp mailing sales 2008-1-27 15:13:37.207
108740 comp mailing usages 2008-1-27 16:44:17.517
108261 comp mailing stats 2008-1-27 14:42:55.780

so i want maxcompmailid for each date
so output will be like:

compmailid compmaildetail compmaildate
-------------------------------------------
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108740 comp mailing usages 2008-1-27 16:44:17.517

if i m doing:

select max(compmailid),compmaildetail,max(compmaildate) from compmail
group by compmaildetail

not giving me correct results which i want

can anyone have idea?

View 4 Replies


ADVERTISEMENT

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

Minimum/Maximum Of Two Values

Dec 6, 2004

Hallo Cracks,

what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.

What I try to do:

I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):

Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)

like this sqlstatement:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)

instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.

thanks in advance and kind regards :-)
Cappu

View 2 Replies View Related

Get Minimum And Maximum Values From Field?

Sep 16, 2012

I am trying to get the minimum and maximum values from a field in SQL Server 2008 Express, but I cannot even get started because I keep getting this error that I cannot figure out.

View 6 Replies View Related

Sum And Select Maximum Values From Table

Feb 13, 2014

I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?

select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100),
convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded

View 4 Replies View Related

Retrieving Maximum Of Two Column Values

Oct 8, 2007



Hi,

I have a table with 3 colums and col1 and col2 are datetime columns as follows

ID Col1 Col2
----------------------------------------
1 D1 NULL
2 D2 D3
3 NULL D4

I want to retrive the ID along with the latest date from either of remaining columns.

Can any one let me know the query please??
~Mohan

View 6 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

T-SQL (SS2K8) :: Getting Minimum And Maximum Values In A Large Table

May 23, 2014

Table definition:

Create table code (
id identity(1,1)
code
parentcode
internalreference)

There are other columns but I have omitted them for clarity.

The clustered index is on the ID.

There are indexes on the code, parentcode and internalreference columns.

The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!

Example:
ID| Code| ParentCode| InternalReference|
1 | M111| NULL | 1|
2 | AAA | M111 | 2|
3 | .... | .... | ....|
4 | AAB | M111 | 2000|
5 | M222 | NULL | 2001|
6 | ZZZ | M222 | 2002|
7 | .... | .... | .... |
8 | ZZA | M222 | 4000|

The table currently holds around 300 millions rows.

The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:

--Find first internalrefernce
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference

-- Find last ineternalreference
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference DESC

These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.

I realize the best way to fix this is to redesign the table, but I cannot do that at this time.

Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?

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

Transact SQL :: Error - Maximum Row Size Exceeds Allowed Maximum Of 8060 Bytes

Sep 12, 2015

I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.

“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.

My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.

I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.

DECLARE              
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),

[code]....

View 5 Replies View Related

Error - Maximum Row Size Exceed Allowed Maximum Of 8060 Bytes

Apr 20, 2012

I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -

Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.

INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

how can i resolve this?

View 14 Replies View Related

Compare DateTime Values

Feb 2, 2006

guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated

Create Procedure dbo.My_Time_Check
@myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @myDate
--would like to update these two columns if rows are found

thanks again
this forum is the best
rik

View 5 Replies View Related

DateTime Values In SQL Express ASPNETDB.MDF

Apr 18, 2007

greets again folks,
The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.
The date is usually correct but the time is always hours off.
Is there some way to get the time part of the DateTime to be correct?
Do I have to write code to set the time when the user logs in?
Thanks a mil!

View 9 Replies View Related

Check How Much Two Datetime Values Overlap

Jan 30, 2008

How can i return how much of the timespan in table2 is in the timespan in table1?

table 1 - email
-------
Login time: 2007-12-12 13:14:26.363
Logout time: 2007-12-12 14:15:58.803

table2 - phone
------
Login time: 2007-12-12 12:11:08.343
Logout time: 2007-12-12 14:13:10.847

View 9 Replies View Related

BCP In Datetime Values In Nondefault Format.

Nov 2, 1998

How Can I BCP in datetime values in nondefault format, wich is "mdy" in U.S. English.

Thanks in advance, for any help.

View 1 Replies View Related

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 View Related

Get Latest Values From Database With Max Datetime

Sep 12, 2006

Hi,

I have two tables TABLEA and TABLEB. They both have the same columns. I need to insert data from TABLEA to TABLEB based on the following conditions

TABLEA Structure with Sample Data






PKEY

Attribute

Value

Updated_DateTime


1

A

2.2

08:30AM


2

A

3.3

08:40AM


3

B

3.2

08:40AM


4

C

1.1

08:00AM


5

C

1.2

08:43PM


6

D

1.01

08:42AM

This is the sample data for TABLEB






PKEY

Attribute

Value


01

A

2.2


02

F

3.3


03

B

3.2


04

D

1.01



Now..When inserting data into TABLE B ,

->I need to check if the Attribute already exists in TABLE B .

-> If it already exists then I must update the Value feild with the latest value from TABLEA based on updated_datetime.

(Ex: Attribute 'A' already exists in tableB so i need to update the VALUE feild in TABLE B to 3.3 which is the latest updated value in TABLEA at 08:40AM)

-> If the Attribute doesnt exist then I simply need to insert that row into TABLEB

Can some one please post the query or the procedure that I can use for the above requirement.

Any help greatly appreciated.This is a urgent requirement for me.Please help

Thanks

View 1 Replies View Related

Problem With Storing Datetime Values In Formview

Jan 20, 2007

Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get  an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now:   Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>"                            SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number">            <DeleteParameters>                <asp:Parameter Name="Ticket_number" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="column1" Type="Int32" />                <asp:Parameter Name="Employee_ID" Type="Int32" />                <asp:Parameter Name="Problem_description" Type="String" />                <asp:Parameter Name="Date_submitted" Type="String" />                <asp:Parameter Name="Ticket_number" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="column1" Type="Int32" />                <asp:Parameter Name="Employee_ID" Type="Int32" />                <asp:Parameter Name="Problem_description" Type="String" />                <asp:Parameter Name="Date_submitted" Type="String" />            </InsertParameters>        </asp:SqlDataSource>    I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate:     <InsertItemTemplate>                column1:                <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'>                </asp:TextBox><br />                Employee_ID:                <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'>                </asp:TextBox><br />                Problem_description:                <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'>                </asp:TextBox><br />                                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"                    Text="Insert">                </asp:LinkButton>                <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                    Text="Cancel">                </asp:LinkButton>            </InsertItemTemplate>            <ItemTemplate>                column1:                <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br />                Employee_ID:                <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'>                </asp:Label><br />                Problem_description:                <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'>                </asp:Label><br />                Date_submitted:                <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'>                </asp:Label><br />                Ticket_number:                <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'>                </asp:Label><br />                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"                    Text="Edit">                </asp:LinkButton>                <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"                    Text="Delete">                </asp:LinkButton>                <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"                    Text="New">                </asp:LinkButton>            </ItemTemplate>        </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work.     Any help would be greatly appreciated. Thanks! 

View 2 Replies View Related

How Do You Handle Null DateTime Values From SQL Server?

Aug 17, 2005

If myDateTimeColumn contains a <NULL> value.  How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types.
I am curious as to what others are doing to handle this?

View 6 Replies View Related

Null Values In Datetime Fields, Howto?

Jul 12, 1999

Hi,
When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?

View 1 Replies View Related

Is There An Aggregate Function To Sum A Datetime Field Values?

Jan 15, 2004

Hi all...

how I can obtain the sum of a datetime field as aggregate function?

Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?

Thanks..

Massimo

View 7 Replies View Related

T-SQL (SS2K8) :: Getting Sum Values For Non Overlapping Rows By Datetime?

Oct 13, 2014

I want to count persons for non overlapping intervalls by room number. Here the data:

SET DATEFORMAT mdy;
DECLARE @PersonTime TABLE
(
ID INT,
Person INT,
Room INT,
Coming DATETIME,
Going DATETIME

[code]....

View 4 Replies View Related

Counting The Years In Multiple Datetime Values

Jul 19, 2007

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance

View 8 Replies View Related

...Contains More Than The Maximum Number Of Prefixes. The Maximum Is 3.

Oct 10, 2005

SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward

View 2 Replies View Related

Mssql Won't Find NULL Values In Datetime Field??

Feb 13, 2007

Hi

I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:

SELECT * FROM ADMIN WHERE date_edited = NULL

I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:

thanks

View 10 Replies View Related

How To Convert UTC Based Datetime Values To Local Setting?

Sep 11, 2007

Hello:

I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).

So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.

I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.

Any suggestions or links to code will be greatly appreciated!

Thanks,
Pete

View 5 Replies View Related

Converting UTC Datetime Values To Local Time Zones

May 20, 2008



I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:


SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4


-- =============================================

-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO

View 6 Replies View Related

Query To Get Values From Datetime Column Into Comma Separated Text

Dec 20, 2005

Hi All
I am working on a query to get all the datetime values in a column in a table into a comma separated text.
eg.
     ColumnDate                                                  --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000
I wanted to get something like
2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 
Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks

View 1 Replies View Related

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

Transact SQL :: Only Store Datetime Values Down To Nearest Minute Automatically Without Using Trigger

Sep 25, 2015

Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?  

Here is a very basic example of what I am trying to do:

--example:  '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate() 

 --example: '2007-09-22 15:07:00.000'  this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)

View 24 Replies View Related

Inserting Datetime Through Sqldatasource - String Was Not Recognized As A Valid DateTime

Dec 6, 2006

I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter: 
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
 Running sql2005

View 1 Replies View Related

DateTime Unable To Save In Datetime Field Of SQL Database

Mar 14, 2007

 Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up  Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar.  Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated 

View 4 Replies View Related

SQL Query: Finding Records Between Datetime Inside Datetime

Mar 17, 2007

Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int)     |    stamp_start (Type: DateTime)        |      stamp_end (Type: DateTime)           |      Username (varchar)0             |      17-03-07 12:00:00                      |            17-03-07 12:30:00                     |     Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
 Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark

View 2 Replies View Related







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