Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

reading time betwee 1.00 am and 07.00 am

Status
Not open for further replies.

NAMARA

Instructor
Dec 7, 2012
8
UG
How can I use a select query to read time between 1000 am and 07.00am, i have a table where time stamp is entered, but when i check values in that table where time has been automatically calculated we have no Am sysmbol,how shoul I go about strictly reading those valuse entered after mid night but before 07.00 am in the moring?
 
If you don't have AM or PM, then SQL server will assume military time, where times after noon have 12 added to them. For example, 1:00 PM = 13:00, 6:00 PM = 18:00 etc...



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
function datediff will give you time difference regardles of how you store data...
 
Between 10 AM and 7 AM?
Is 7 AM next day or I'm missing something?

Can you post some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server
 
If it is Between 10 AM and 7 AM next day it should be 2 intervals
 
I think 1000 AM is a typo for 1.00 AM, based on the title of the post.

Tamar
 
What is the type of the field?
Do you need records from 7:15, 7:34.. or you need records till 06:59:59?

Borislav Borissov
VFP9 SP2, SQL Server
 
I have a table called :[order] storing data for 24 hrs,the date and time stamp is stored in a field called [ordertime],I have another field called [date] where I store date only,now when the time reaches mid night, the [date] value changes to the next date, However I would like to reset this date to the Previous date, for the time between 1.00 AM and 7.00 AM in the morning,

Im trying to use this sql statement

update [ORDER]
set [date] = [date] - 1
where DATEPART(hh,ordertime) between 0 and 7

The table name is [order],the fields are:[date] to store the date when information has been entered and [ordertime] to store the date and time.

unfortunately, this routine will also reset the date even for data entered say at 5.0 PM(evening)

Sample data:
ordertime
20/04/2014 16:24:12
20/04/2014 16:24:18
20/04/2014 16:24:27

date
20/04/2014
20/04/2014
20/04/2014
 
Hi,

What about
Code:
set date = ttod(yourdatetimefield - 420)
hth
MK
 
Hi,

The code above works for VFP - I don't know if there exists is an equivalent function for SQL Server
Sorry

MK
 
try that

SQL:
update [ORDER]
set [date] = [date] - 1
where DATEDIFF(MINUTE, [date] , ordertime) < 420
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top