What is the best method for ignoring the time in datetime comparisons. Say I want all records on 07/08/1996 regardless of their time. Or all records between 01/01/1999 and 04/01/1999 even if one of the records on 04/01/1999 had a time of 16:32:22
View 5 RepliesI have a PHP page where the user enters a date that represents the last day of a timesheet (ts_end) and the hours worked on that timesheet. That is then written into a table where the date is a datetime type. Because the user just enters a date, the time portion of the field is set to 00:00:00. In another place, I need to sum the columns for reports submitted between the beginning of a timesheet (ts_end -6 days) and the ts_end date.
The problem is that chartreviewed values entered on the ts_end date are getting lost because the time part of the ts_end field is 00:00:00 and the time part of the dateentered for the chartreviewed value is not. For instance using 2/4/2004 as the ts_end date looses the 192 charts.
Reporter activity chartsdateentered
2001576 20672 563 2004-01-29 13:55:51.000
2001576 20665 202 2004-02-02 19:54:57.000
2001576 20666 160 2004-02-03 22:48:11.000
2001576 20667 192 2004-02-04 19:41:51.000
I know I can revise the query to look for charts where the dateentered is less than dateadd(d,1,ts_end) and get the right values. It seems like there has to be a way though to tell sqlserver to ignore the time part of a datetime field when querying.
Anybody know what it is?
Thanks,
Ursus
got a quick question guys.
if i use this to parse the current date to the right side of the time.
right(getdate(),7) - i'll get something like 7:30AM.
i also have Times stored in a column of a table, but as a string not a date time.
it seems to compare okay, but when the time is say 1:30PM and im comparing it if its greater than or equal to (>=)to 7:30AM - it doesnt return.
i think its ignoring the AM/PM Meridian Values and just comparing the numbers.
is there a conversion i could use to do this?
ive tried a military time conversion i found but it converts to hrs,min,milliseconds.
convert(char(8),(convert(datetime,current_timestam p,113)),114)
if anyone knows a good way to do this - i would appreciate it.
thanks again
rik
I'm using Microsoft SQL Server Management Studio. I've created a view (very basic two field select with a inner join) -- I expect the query to take some time as there are about 1.7 million records in one table and about 3 million in the join table. Problem is, I always get a timeout error when I try to run the query.
I've checked the server settings and "Execution time-out" = 0 (no time-out). Checked this at the server and in Microsoft SQL Server Management Studio. So my question is why am I getting a time-out when I have it set to not time-out?
What is going on?
Thanks, Rob.
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies View Relatede.g.
1st March 2005 12:00:00
is showing as
01/03/2005 00:00:00
instead of
01/03/2005
Why does this happen?
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
Hi
i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.
the timezone field has values in minutes that i should add to my datetime field so i have the actual time.
afterwards i split the datetime into date and time.
the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).
could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?
i do all the calculations in my datasource (sql).
Thanks
V.
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 RelatedThis question is from a deveoper that I work with:
In SQL Server 7.0:
Do you know of a query or sp which will return the list of objects in a DB, sorted in descending order by last changed date?
I need to generate a list of all the stored procedures created or modified since a specified date. I can get the created ones, but I can't see how to get the modified ones.
Thanks!
Any ideas on how to tackle this one?
Thanks,
Brad
I have implemented a login audit on a particular system which catches the users login details, including their application logon name and NT username.
What I want to do is report on users who have logged on to the software using someone else's workstation (i.e. logged on to more than one workstation).
Here's some sample stuff to play with
DECLARE @logins table (
loginName char(20)
, ntUsername char(25)
, loginDate datetime
)
--Insert test data. Please note that loginName and ntUsername are rarely the same
INSERT INTO @logins (loginName, ntUsername, loginDate)
SELECT 'Amy', 'Amy', '20070101' UNION
SELECT 'Amy', 'Amy', '20070102' UNION
SELECT 'Amy', 'Amy', '20070103' UNION
SELECT 'Bob', 'Bob', '20070101' UNION
SELECT 'Bob', 'Bob', '20070102' UNION
SELECT 'Bob', 'Amy', '20070103' UNION --Bob has logged on using 2 different NT accounts
SELECT 'Cal', 'Cal', '20070102' UNION
SELECT 'Cal', 'Amy', '20070102' UNION --So has cal
SELECT 'Dom', 'Dom', '20070102' UNION
SELECT 'Dom', 'Dom', '20070102'
Any ideas? I just can't think of the logic needed to get what I want.
Any extra info needed - just ask!
Cheers
I have the following query below that I am trying to get working. What I want it to do is check for users who have sat a module and failed it and compare it to a table to check that they have not passed the module second time and report only those who have failed withg no passes. Query below.
SELECT DISTINCT dbo.PPS_SCOS.NAME, PPS_PRINCIPALS.NAME, pps_transcripts.date_created, score, max_score, status
FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
WHERE dbo.PPS_SCOS.NAME LIKE 'MTB-S001%'
AND PPS_PRINCIPALS.LOGIN LIKE '%test%'
AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'F'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND dbo.PPS_PRINCIPALS.NAME NOT IN (
SELECT DISTINCT dbo.PPS_SCOS.NAME FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
WHERE dbo.PPS_TRANSCRIPTS.STATUS LIKE 'P'
AND dbo.PPS_SCOS.NAME LIKE 'MTB-S001%'
AND PPS_PRINCIPALS.LOGIN LIKE '%test%'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%' )
ORDER BY pps_PRINCIPALS.NAME
Any help appreciated.
Thanks
Sorry if this has been asked befor, but I couldn't find anything that hepled me.
I have a datetime field and want to extract only the time. I know it works with CONVERT and style-type 108. But I get something like this: 08:00:00 and I need something like this: 08:00.
Can this somehow be done?
Michael
Is here an easy way to get 9:00 PM from 5/1/2005 9:00:00 PM withoutdoing:Cast(DATEPART(HOUR,xDate) as varchar(2)) + ':' +Cast(DATEPART(MINUTE,xDate) as varchar(2))lq
View 3 Replies View Related Hi,
Im moving data from a OLE DB Source to a Flat File Destination.
I have a DateTime field in my database.
My current query returns:
2007-05-21 00:00:00
How can I make it return:
2007-05-21
Thank you!!
I am student programmer attending University of Houston.
I am having a problem with datatime function.
Example:
I have a test data below
1;1;1;7/1/2003;1,
I am using a bulk script to import all this test data from a text file in to SQL.
When I view the data in the SQL it give me the date as 7/1/2003 12:00:00 AM.
I would like to remove the 12:00:00 AM and just have the date. Is thie possible?
I've been reading a bit about full-text searches, phonetic values and match-queries and just don't know where to begin.
What I'm eventually going to do, is make procedures for matching names, finding records that are close matches and presenting them in a subform below the actual member that you look up.
E.g. if an employee looks up Sergej, he or she will also see Sergey, Sergei etc. below the membersheet.
BOL isn't very practical in examples, and its about 7 years since I took my SQL-Server 7.0 MS courses, plus I've primarily worked as an administrator up until last fall, not a developer. So where to begin?
Thanks in advance,
Trin
hallo i have an expression like this
CASE
when (a1<>a2 AND b1=b2 AND c1=c2) then...
when (a1=a2 AND b1<>b2 AND c1=c2) then...
when (a1= a2 AND b1=b2 AND c1<>c2) then...
is there any more elegant/compact/fast way to write this?
Hi all,
I am generating a report dynamically using ReportViewer control. I use a SELECT query to fill the dataset which in turn is used by the ReportViewer to display records. One of the fields is [End Date] and is a DateTime in SQL Server. When the ReportViewer is populated, the End Date field shows the time also with the date. How can I remove that time part? I have used these methods and I want to keep the value as DateTime only and not convert to a string or something.
These don't work for me: CAST(FLOOR(CAST(m.[End Date] AS float)) AS datetime)
DATEADD(day, DATEDIFF(day, '20000101', [End Date]), '20000101')
dateadd(day,datediff(day,0,[End Date]),0)
Please can someone help.
Thanks.
dear friends.................. i have a table like below.. name date ------------------------------------------------------------------------------------------------------------ anbumani 2/18/2008 4:15:56 PM anbumani 2/18/2008 4:21:29 PM anbumani 2/18/2008 4:23:03 PM anbumani 2/18/2008 4:25:25 PM i want a out put as only time (ex : 4:15:56 PM 4:21:29 PM 4:23:03 PM ) give me the stored procedure code in sql server 2005.. thanks and regards Samuel Chandradoss . J
View 3 Replies View Related
I looked at this post and it didn't work for me.
Trying to extract the time portion from a datetime field. ex 7:15:12 AM from 1/1/2008 7:15:12 aM
This is what I tried and neither functions give me the results I want....
SELECT DueTime, CONVERT(varchar, DueDate, 108) AS Expr1, SUBSTRING(CAST(DueDate AS varchar), 10, LEN(DueDate)) AS Expr2, CAST(DueDate AS varchar) AS Expr3FROM TODO
Due Time Expr1 Expr2 Expr3
1/1/2008 5:15:00 PM 00:00:00 12:00:00 AM Jan 1 2008 12:00:00 AM
It seems Cast(DueDate as varchar) is the problem it converts the time to the default 12:00:00 AM
In the above example I want 5:15:00 PM
Tx in advance
Hi Guys,
I have to take only time from DateTime column filed value.
Is there any function in SQL Server which will give us time only.
Thanks,
Rau
I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.
The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date
I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.
Thanks,
Carl
Hi there i have a column with datetime field...
i want to extract only time field to the new column...
Start Date
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:30
8/24/2007 11:00
8/24/2007 11:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 13:00
8/24/2007 14:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 17:00
8/24/2007 20:00
i want something like
StartDate Start time
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:30
8/24/2007 11:00
8/24/2007 11:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 13:00
8/24/2007 14:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 17:00
8/24/2007 20:00
can anyone plz find the solution for this one.
Thanks a lot!!!
pavan
I have a function that accepts 2 DATETIME parameters. When the user passes a time other than midnight, the function returns a different result. Is there a simple way to strip the time from the variables?
Here's the declaration:
ALTER FUNCTION [dbo].[NumBusinessDays]
(
-- Add the parameters for the function here
@StartDate DATETIME,
@EndDate DATETIME
)
Hi,
I have a DateTime Column and I need to update the Time Part of it, without changing the Date Part.
For Example:
STARTDATE:
04.08.2008 12:30:00
UPDATE TO:
04.08.2008 14:40:00
I tried something like:
UPDATE table SET STARTDATE = '14:40:00' WHERE GUID = '{82F99509-3C44-4D24-96D0-CF3753C0C353}'
But this will also change the Date to 1.1.1900.
Any advise?
This looks like a bug - hopefully somebody can explain what is actuallyhappening. Using SQL Server 2000 SP4.Here's a repro script with comments:/* repro table */CREATE TABLE dbo.T (ID int NOT NULL,Time datetime NOT NULL,CONSTRAINT PK_T PRIMARY KEY (ID, Time))GO/* the problem does not happen without this index */CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)GO/*sample row - note thatCAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'*/INSERT INTO dbo.T (ID, Time)VALUES (1, '2006-04-08 13:14:58.870')GO/*This does not return any rows - why?The comparison should evaluate to TRUE.*/SELECT *FROM dbo.TWHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'GO/*This does return the row.*/SELECT *FROM dbo.TWHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >='2006-04-08 13:15:00'GODROP TABLE dbo.TGOThe difference between the two SELECT statements is that the first one usesa non-clustered index seek, whereas the second one uses a scan of the sameindex.--(remove a 9 to reply by email)
View 5 Replies View RelatedI am trying to compare the ADDRESS FIELDS Between 2 tables in SQL SERVER 2008. However when I run the comparisons below it throws the error below:
Query:
select
inner
JOIN TABLE2 B
ON
COLLOAN=
COLLOAN1
a.ADDRESS<>b.PropertyAdd
Error : Cannot resolve the collation conflict between "SQL_Latin1_General_Pref_CP437_CI_AS" and "SQL_Latin1_General_CP850_BIN" in the equal to operation.
WHERE
A.Address ,b.PropertyAdd
,a.*
from TABL1 A
Is there a way to store a time in the SQLServer database, without the date? (e.g. just simply "HH:MM" instead of "HH:MM DD/MM/YYYY" which it stores it in)
View 1 Replies View Related
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do......
select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it
whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level
any ideas??????????
Hi everyone, this is the exception:
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
Here's my code!
... 'datagrid code
cmdSelect.CommandText = "SELECT * FROM [Table] WHERE [Date and Time] > '" & Me.tbFromDate.Text & "' AND [Date and Time] < '" & Me.tbToDate.Text & "'"
...
Me.tbFromDate.Text and Me.tbFromDate.text look like this: 27/11/2003 1:11:43 PM
The SQL Query works fine - just that this code gives a runtime error.
Can someone help?? I'm guessing I have to do somekind of conversion!!
Andrew
Hi All,
I have a SQl Statemnent .... AND dbStartDateTime >= 27/12/2003 19:00:00 .... and get the following error
Incorrect syntax near '19'
I dont understand why it wouldnt like this as this is how it is stored in the database?
I have looked for answers but most people seem to want ot use DateTime without the time
Thanks in advance
Lee
I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?
View 2 Replies View Related