DATE CHANGE IN DATE PART - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

DATE CHANGE IN DATE PART

Author

Topic

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-28 : 06:45:00

Hi,
I am getting the yesterday's date for datepart function given below. I want the today's date. is it possbile?

where (DATEPART(HOUR, LOGTIME) IN (23, 0))

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-28 : 08:36:59

DATEADD(day,DATEDIFF(day,0, GETDATE()),0) will give you todays date so i think what you may be looking at is below condition

LOGTIME > = DATEADD(day,DATEDIFF(day,0, GETDATE()),0) AND LOGTIME < DATEADD(day,DATEDIFF(day,0, GETDATE())+1,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-28 : 08:37:26

also see
http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 05:21:08

Sorry today's date means
suppose if I am having data for 23/2/2010 and 24/2/2010 and if I give this datepart function
where(DATEPART(HOUR, LOGTIME) IN (23, 0))
I am getting the result with date as 23/2/2010. Instead I need the date as 24/2/2010

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 05:25:31

quote:
Originally posted by smitha

Sorry today's date means
suppose if I am having data for 23/2/2010 and 24/2/2010 and if I give this datepart function
where(DATEPART(HOUR, LOGTIME) IN (23, 0))
I am getting the result with date as 23/2/2010. Instead I need the date as 24/2/2010

Smitha

you want logtime for today date at 0 and 23 hour ?


where logtime >= dateadd(day, datediff(day, 0, getdate()), 0)
and   logtime <  dateadd(day, datediff(day, 0, getdate()), 1)
and   datepart(hour, logtime) in (0, 23)

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 05:50:01

no, I want the data from 23 to 0 hour with tommorrow's date.

quote:
Originally posted by khtan

quote:
Originally posted by smitha

Sorry today's date means
suppose if I am having data for 23/2/2010 and 24/2/2010 and if I give this datepart function
where(DATEPART(HOUR, LOGTIME) IN (23, 0))
I am getting the result with date as 23/2/2010. Instead I need the date as 24/2/2010

Smitha

you want logtime for today date at 0 and 23 hour ?

where logtime >= dateadd(day, datediff(day, 0, getdate()), 0)
and logtime < dateadd(day, datediff(day, 0, getdate()), 1)
and datepart(hour, logtime) in (0, 23)

KH
[spoiler]Time is always against us[/spoiler]

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 06:13:26

[code]
where logtime >= dateadd(hour, 23, datediff(day, 0, getdate()))
and logtime < dateadd(day, datediff(day, 0, getdate()), 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 07:28:00

I am giving my query and the result

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108) AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (0, 1))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)
Result:
10/02/2010 00:00:00

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 09:01:57

quote:
Originally posted by smitha

I am giving my query and the result

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108) AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (0, 1))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)
Result:
10/02/2010 00:00:00

Smitha

Don't are you trying to tell us here ?

Can you please post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 10:30:02

with this query, in the result instead of date as 10/2/2010 I want the date as 11/2/2010

quote:
Originally posted by smitha

I am giving my query and the result

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108) AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (0, 1))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)
Result:
10/02/2010 00:00:00

Smitha

Smitha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 11:05:32

[code]LOGTIME > = DATEADD(hh,-1,DATEADD(day,DATEDIFF(day,0, GETDATE())+1,0)) AND LOGTIME <= DATEADD(day,DATEDIFF(day,0, GETDATE())+1,0)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 11:05:52

Can you please post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-01 : 11:22:00

the data is
10/02/2010 23:00:00 12.000000
10/02/2010 00:00:00 12.000000
11/02/2010 01:00:00 12.000000
11/02/2010 02:00:00 12.000000

for 00:00:00 I am getting the date as 10/02/2010 instead I require 11/02/2010

quote:
Originally posted by khtan

Can you please post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-03-02 : 03:21:47

can anyone help on this??

quote:
Originally posted by smitha

the data is
10/02/2010 23:00:00 12.000000
10/02/2010 00:00:00 12.000000
11/02/2010 01:00:00 12.000000
11/02/2010 02:00:00 12.000000

for 00:00:00 I am getting the date as 10/02/2010 instead I require 11/02/2010
quote:
Originally posted by khtan

Can you please post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Smitha

Smitha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-02 : 04:28:05

quote:
Originally posted by smitha

the data is
10/02/2010 23:00:00 12.000000
10/02/2010 00:00:00 12.000000
11/02/2010 01:00:00 12.000000
11/02/2010 02:00:00 12.000000

for 00:00:00 I am getting the date as 10/02/2010 instead I require 11/02/2010
quote:
Originally posted by khtan

Can you please post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Smitha

What is the business rule ? What is the expected result ? for the sample data you posted ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources