T-SQL (SS2K8) :: How To Round A Date To The Nearest Second

Sep 8, 2014

how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.

Also, the solution should not be too cumbersome because it will be used in a high volume environment.

View 5 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Round Value To The Nearest

Sep 16, 2014

I have a column called as NDM$ What I want do it round it the nearest value example I am giving below

34.100->34%

39.8->40

35.4->35 some thing like that.

View 6 Replies View Related

Round To Nearest WHOLE Number

Nov 16, 2006

T-SQL:How to round to the nearest WHOLE number ?sofrom -- to-------------170 --17096.58 --97thanks

View 1 Replies View Related

T-SQL (SS2K8) :: Date Comparison In Two Table By Returning Nearest Date Of Table A In Table B

Jun 9, 2014

I am having a problem in creating query for this exciting scenario.

Table A

ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000

Table B

IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000

I would like to return the nearest date of Table B in my table like for

ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

View 3 Replies View Related

T-SQL (SS2K8) :: Nearest Neighbor Within Given Shape Query

Nov 24, 2014

I have a table with 257 mil records with latitude and longitude data.

My goal is to find the closest intersecting values from a locations table (88 rows) and update any of the 257 mil records that are applicable with the location_Name and Location_Group_Name.

The query I have works but doesn't perform well on such a big data set.

CREATE TABLE #Positions -- Base table 257 mil rows. Actual table has 20 columns
(
IDBigInt PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),

[Code] ....

Attached you will find the tables, test data, a function to measure distance and some queries that work but are too slow for this much data.

View 9 Replies View Related

T-SQL (SS2K8) :: Rounding Decimal To Nearest Integer Value?

Jun 28, 2015

how to round the nearest value after round of decimal and return integer.

declare @data decimal(18,2)
set @data = 5.55

-- output

select '5'
set @data = 5.58
-- output
select '6'

View 3 Replies View Related

List By Nearest Date?

Oct 7, 2006

Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.

I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.

I'm having some problems, currently I have this statement:


Code:


DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)


INSERT INTO @tbl
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) >= DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)


INSERT INTO @tbl -- those are the one who allready have had birthday this year
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) < DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)



SELECT * FROM @tbl





It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.

I thought about adding an additional check:
Code:

AND DATEPART(day, dato) >= DATEPART(day, getdate())

in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.

Anyone have any suggestions? Is there an easier way to do it?

Dag

View 7 Replies View Related

T-SQL (SS2K8) :: Calculation For Each Value By A Percentage Using Round

Jul 23, 2014

I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values ​​for the percentage (also using round).

IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo
Create Table #Redondeo (Orden int Identity(1,1),Valores money)
Insert into #Redondeo
Select 71374.24 Union Select 16455.92
Union Select 56454.20 Union Select 9495.18

[Code] ...

RESULT: 177673.74; 37311.49

View 6 Replies View Related

Optimal Search For The Nearest Date

Nov 23, 2005

I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated

View 1 Replies View Related

T-SQL (SS2K8) :: Difference In Seconds To Round To Minute?

May 20, 2014

I'm trying to get the difference between the two times in minutes with the seconds rounded up or down

As an example the difference in the times:

1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,

using

select
cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))

I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?

View 5 Replies View Related

Find Nearest Date Record Without Cross Apply

Oct 13, 2012

Table :

ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal

Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.

SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date

The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Add Previous Row End Date As Next Row Start Date

Mar 14, 2014

Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.

View 9 Replies View Related

T-SQL (SS2K8) :: Trying To SUM Row With Current Date To Row With Last Month Date

Jul 23, 2014

I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.

=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,

[Code] ....

Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.

SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID

View 9 Replies View Related

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)

Jan 30, 2006

Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt

View 3 Replies View Related

T-SQL (SS2K8) :: Getting UTC Date From Integer?

Dec 29, 2014

I'm trying to get the UTC date from an integer. See my SQL below:

Declare @unix_date bigint
Set @unix_date = 3499288964
SELECT dateadd(day, @unix_date/(86400), dateadd(second, @unix_date
% (86400), '19700101'))

It returns '2080-11-20 00:42:44.000'. This is dead on except for the year (which should be 2014). I was thinking maybe my bigint value of 3499288964 was milliseconds or microseconds so I adjusted the seconds value (86400) in the select statement to reflect milliseconds (86400000) with no success and microseconds (86400000000) with no success as both of those gave incorrect results. Closest I got was with the seconds (86400) which of course returns the incorrect year.

View 2 Replies View Related

Nearest Distance

Jan 21, 2004

Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated

View 12 Replies View Related

T-SQL (SS2K8) :: Count Events On Date

May 12, 2014

I have a table containing some events. Those events all have a start date and an end date.

CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date

[Code] ....

When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.

View 9 Replies View Related

T-SQL (SS2K8) :: Date Format Conversion

Jun 17, 2014

i am trying to convert a string like this 'le dd/mm/yyyy' into a datetime.I have removed the 'le ' part and used covert(datetime, 'dd/mm/yyyy',103) to convert into datetime. This works for example for 'le 22/11/1799' but for 'le 09/11/1716' it does not work.

select convert(datetime,RIGHT('le 22/11/1799', LEN('le 22/11/1799') - 3), 103) -> it works
select convert(datetime,RIGHT('le 09/11/1716', LEN('le 09/11/1716') - 3), 103) -> it does not work

View 3 Replies View Related

T-SQL (SS2K8) :: First And Last Day Of The Month In A Given Date Range?

Jun 26, 2014

I would like to get the first and last day of any month in a given date range.

Ex: Display the first and last day of the months between @startDate and @EndDates.

Input Params= @StartDate='2016-06-21 16:57:11.093'
@EndDate = '2016-09-30 00:00:00.000'

OutPut should be:-

MonthStartDateMonthEndDate
1/06/201630/06/2016
1/07/201631/07/2016
1/08/201631/08/2016
1/09/201630/09/2016

View 1 Replies View Related

T-SQL (SS2K8) :: Convert String To Date?

Sep 5, 2014

I'm importing dates into a table with Bulk insert

SET DATEFORMAT DMY

it works with dates e.g. "14/01/2009"

However sometimes I get dates in the format

"Fri 14/01/2009"

What is the best way to convert these.

I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields

LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))

View 9 Replies View Related

T-SQL (SS2K8) :: Get Earliest Date From Two Dates

Dec 10, 2014

I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null

I tried..

DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Date Column With 1900-01-01 Value

Dec 23, 2014

I have a date column with 1900-01-01 value, I am trying to get the min(date) without 1900-01-01.My problem here is: there are some records with both 1900-01-01 and real date. How do I get the MIN of the real date without getting 1900-01-01. Below is my query sample.

CASE WHEN MIN(CAST(PROJECTEDSTARTDATE AS DATE)) = '1900-01-01' THEN NULL ELSE MIN(CAST(PROJECTEDSTARTDATE AS DATE)) END as 'ProjectStartDate'

View 3 Replies View Related

T-SQL (SS2K8) :: How To Get Current Date Between Last 24 Hours

Jan 14, 2015

I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:

SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]

I know I am not doing the between correctly to get the changedate between the last 24 hours. Is there a way to correct this so that I am only getting the change date that is between 3am today and 3am yesterday on any given day I happen to run this?

View 7 Replies View Related

Rounding To The Nearest Thousand

May 11, 2007

Hi

Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?

Thanks,
Neil

View 7 Replies View Related

T-SQL (SS2K8) :: Formatting Date Of Birth Using RSA ID Number

Nov 8, 2012

I need creating date of birth using ID number the ouput that im looking is a follows

e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01

The desired format I need is to take the above and create date of birth with the below format as required by the application used.

01 Jan 1980
01 Jan 2000

View 9 Replies View Related

T-SQL (SS2K8) :: Finding Gaps Within Date Ranges

Sep 13, 2013

I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.

Example dataset SQL below:

CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'

[Code] ....

I would expect a dataset to be returned consisting of:

1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013

I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...

View 9 Replies View Related

T-SQL (SS2K8) :: Date Format - Convert Function

Mar 12, 2014

How to get this out put.

Details:

declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM

declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert

--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM

What is the correct date format to achieve this.

View 2 Replies View Related

T-SQL (SS2K8) :: Get Start And End Date Based On A Criteria?

Apr 17, 2014

I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .

Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')

[code]....

Expected Output

---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'

View 9 Replies View Related

T-SQL (SS2K8) :: How Many Users Are Working Till Specify Date

Apr 30, 2014

I have a table with 5000 rows history about working and retired time.

It is the several rows from table(User, StartDate, EndDate):

User1 2011-05-09 00:00 2014-01-17 00:00
User2 2012-07-01 00:00 2012-08-20 00:00
User2 2013-08-26 00:00 2013-09-02 00:00
User2 2013-10-07 00:00 NULL
User3 2013-09-01 00:00 2014-04-07 00:00
User3 2014-04-08 00:00 NULL

How many users have worked and have retired by years and months,

Example:
2011-01 working 2000
2011-02 retired -50
2011-02 working 1950
2011-02 retired -27
and etc,,

Does it need to join to a time dimension table?

View 7 Replies View Related

T-SQL (SS2K8) :: Order By On Date Descending In A Column

Jun 18, 2014

Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...

IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Preparing Date Schedule - Repayment

Jun 20, 2014

I need the script for the following schedule.

if the repayment start date = 05-01-2016 and installment=5 then result will be
05-01-2016
05-02-2016
05-03-2016
05-04-2016
05-05-2016

if the repayment start date = 30-01-2016 and installment=5 then result will be
30-01-2016
29-02-2016
30-03-2016
30-04-2016
30-05-2016

if the repayment start date = 31-01-2016 and installment=5 then result will be
31-01-2016
29-02-2016
31-03-2016
30-04-2016
31-05-2016

if the repayment start date = 29-02-2016 and installment=5 then result will be
29-02-2016
31-03-2016
30-04-2016
31-05-2016
30-06-2016

if the repayment start date = 28-02-2015 and installment=5 then result will be
28-02-2015
31-03-2015
30-04-2015
31-05-2015
30-06-2015

I have find some ways but unable to make it.

View 7 Replies View Related

T-SQL (SS2K8) :: Join 2 Table Based On Date

Jul 16, 2014

I have 2 tables:

Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....

Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....

I want to create query the output should be:

EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A

View 4 Replies View Related

T-SQL (SS2K8) :: Missing Century In Date String

Jul 31, 2014

Any script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:

7/26/29 = converts to 2029.

I'm looking for a SQL statement that will now to put a 19 or 20 in the century.

View 9 Replies View Related







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