DateTime

Question

DateTime

The_SQL_DBA

SSChampion

Points: 10494
More actions
March 4, 2009 at 6:58 am

#99509

I have a table with a field called 'CallEntry' as Datetime.
I need to generate a report everyday during fixed time from 7 am to 2 pm. How do I convert the date input with time as fixed and variable date then convert it to datetime.
select * from CallerDetail where
CallEntry between ' 03/04/2009 07:00:00' and '03/04/2009 14:00:00'
I am using the below queries but not able to convert them as datetime ??
select DATEADD(hh,DATEDIFF(hh, 0, GETDATE()) - 7 , 0)
select DATEADD(hh,DATEDIFF(hh, 0, GETDATE()) , 0)
Please let me know the way I can input it as datetime.
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

The_SQL_DBA (3/4/2009)
I have a table with a field called 'CallEntry' as Datetime.
I need to generate a report everyday during fixed time from 7 am to 2 pm. How do I convert the date input with time as fixed and variable date then convert it to datetime.
select * from CallerDetail where
CallEntry between ' 03/04/2009 07:00:00' and '03/04/2009 14:00:00'
I am using the below queries but not able to convert them as datetime ??
select DATEADD(hh,DATEDIFF(hh, 0, GETDATE()) - 7 , 0)
select DATEADD(hh,DATEDIFF(hh, 0, GETDATE()) , 0)
Please let me know the way I can input it as datetime.
Thanks!!

Use this conversation:

select CONVERT(VARCHAR,DATEADD(hh,DATEDIFF(hh, 0, GETDATE()) - 7 , 0), 108)

select CONVERT(VARCHAR,DATEADD(hh,DATEDIFF(hh, 0, GETDATE()) , 0), 108)

Hope it will help you, as I understand you problem here!!!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 2

Thanks Dugi..the script runs well but the CallEntry input needs to have the day also in the format m/dd/yyyy hh:mm:ss to be able to return the correct records where the query you gave me just gives me hh:mm:ss..

What am I missing to make sure that the date is derived from getdate and time as fixed..

Thanks again!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

This I think will give you what you need.

-- this is just to check that the CONVERT statements are returning a DATETIME data type and not a VARCHAR

DECLARE @Time DATETIME

--The actual code to use to perform the conversion. The inner CONVERT returns the year, month and day and if converted to a DATETIME data type would be 2009-03-04 00:00:000. So we add 7 hours to that value to arrive at what you, or what I think you want.

SET @Time = CONVERT(DATETIME, CONVERT(VARCHAR(11),GETDATE(),101) + ' 07:00:000')

--just to verfy the result of the CONVERT statements durng testing.

SELECT @Time

Which results in 2009-03-04 07:00:00.000

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

Well thanks for the above script but heres the thing...

I am getting the output as 2009-03-04 07:00:00.000

but my callentry gives me empty data with the above result set

for it to run correctly I need the date entered as '03/03/2009 07:00:00'

I still think there can be a way to do this which I am missing completely..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

The_SQL_DBA (3/4/2009)
Well thanks for the above script but heres the thing...
I am getting the output as 2009-03-04 07:00:00.000
but my callentry gives me empty data with the above result set
for it to run correctly I need the date entered as '03/03/2009 07:00:00'
I still think there can be a way to do this which I am missing completely..
Thanks!!

Are you trying to run this query/report for yesterday between 7 AM and 2 PM, or today?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

[font="Courier New"]

declare @StartDate datetime,

@EndDate datetime;

set @StartDate = dateadd(hh, 7, dateadd(dd, datediff(dd, 0, getdate()), -1));

set @EndDate = dateadd(hh, 7, @StartDate);

select

*

from

dbo.CallerDetail

where

CallEntry between @StartDate and @EndDate;

Does this help?

[/font]

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

Today I am trying to run this query for today..

I want to hard code the time but the date should be retrieved from getdate().

The field takes input as mm/dd/yyyy hh:mm:ss only where dd would change every day which I need to get from getdate

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Since it is for today, change the -1 to 0 in my code in my previous post.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

The_SQL_DBA (3/4/2009)
Today I am trying to run this query for today..
I want to hard code the time but the date should be retrieved from getdate().
The field takes input as mm/dd/yyyy hh:mm:ss only where dd would change every day which I need to get from getdate
Thanks!!

If CallEntry is defined as datetime data type, then all that should be required is that the it be compared to valid datetime data. the format of the date mm/dd/yyyy or yyyy-mm-dd should be irrelavent.

Can you confirm that the CallEntry column is actually defined as a datetime field?

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 10

Wow Lynn this works like a charm..just that I had to take out the -1 and make it a zero as I want to run this the same day..

But this is just great...

Thanks Much!!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 11

CallEntry is a datetime field..but all the previous scripts on this topic couldn't fetch the result set.

Till now I had an inline as below

select * from CallerDetail

where CallEntry between '03/04/2009 07:00:00' and '03/04/2009 14:00:00' and CallerType = 'enrl'

and this fetches 2000 odd rows everyday..

My requirement was to run this everyday without having to change the time and be able to input the date automatically for the each different day..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

What is your normal way of writing dates: mm/dd/yyyy or dd/mm/yyyy. If the later, then that is probably why the dates in character format needed to be that way, so that they would convert properly to datetime format.

The way I setup the datetime range eliminated the need to start with a character formatted datetime.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 13

I normally prefer mm/dd/yyyy way but most of the date fields here at my current job are varchar as 'yyyymmdd' and sometimes I get to work with real datetime fields..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."