reading time betwee 1.00 am and 07.00 am

[NAMARA]

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?

 

[gmmastros]

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

 

[gk53]

function datediff will give you time difference regardles of how you store data...

 

[bborissov]

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

 

[gk53]

If it is Between 10 AM and 7 AM next day it should be 2 intervals

 

[TamarGranor]

I think 1000 AM is a typo for 1.00 AM, based on the title of the post.

Tamar

 

[bborissov]

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

 

[NAMARA]

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

 

[mjcmkrsr]

Hi,

What about

set date = ttod(yourdatetimefield - 420)

hth
MK

 

[mjcmkrsr]

Hi,

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

MK

 

[gk53]

try that

update [ORDER]
set [date] = [date] - 1
where DATEDIFF(MINUTE, [date] , ordertime) < 420