datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

Dates - Subtracting 3 valid days for each row in FactTable

Dear Friends,

I need your support to do a T-SQL...

For each row date field in a FactTable, I need to subtract 3 days or in some cases 2... the problem is not subtracting 3 or 2 days, but I need to verify in other table Holiday to check if some of the 3 previous dates are holiday or not...

In case of one of the 3 previous dates is holiday, I need to subtract 4 days to the main date in spite of 3

In case of two of the 3 previous dates are holidays, I need to subtract 5 days to the main date in spite of 3.

In case of all of the 3 previous dates are holidays, I need to subtract 6 days to the main date in spite of 3.

And I must garantee that I subtract 3 valid dates (not holidays) to the main date... for example, if one of the 3 previous is a holiday, i need to subtract 4 if the 4 day is not holiday... If it's a holiday I need to find the previous valid date...

How can do it? Could you help me?

here you go...

Code Snippet

/*

Create table Holidays

(

HolidayDate datetime

)

Insert Into Holidays values('12/28/2006')

Insert Into Holidays values('12/29/2006')

Insert Into Holidays values('12/30/2006')

Insert Into Holidays values('12/31/2006')

Insert Into Holidays values('01/01/2007')

Insert Into Holidays values('01/02/2007')

Insert Into Holidays values('01/10/2007')

Insert Into Holidays values('01/11/2007')

Insert Into Holidays values('01/20/2007')

Insert Into Holidays values('01/21/2007')

Insert Into Holidays values('01/22/2007')

*/

Create Function dbo.WorkingDateSubtract(@.NumberOfDays int, @.CurrentDate Datetime)

Returns DateTime

as

Begin

If Exists(Select 1 From Holidays Where HolidayDate = Dateadd(DD,-1,@.CurrentDate))

return dbo.WorkingDateSubtract(@.NumberOfDays, DateAdd(DD,-1,@.CurrentDate))

Else

If @.NumberOfDays = 0

return @.CurrentDate

Else

return dbo.WorkingDateSubtract(@.NumberOfDays-1, DateAdd(DD,-1,@.CurrentDate))

return null

End

Go

Select dbo.WorkingDateSubtract(3, '01/03/2007')

|||

Hi PedroCGD,

What about using the date dimension?. You can have a column in the dimension to know if a date is a holiday one or not, in that case you can use:

select

*

from

dbo.FactTable as f

cross apply

(

select top 3

d.date

from

dbo.dim_date as d

where

d.[date] < f.[date]

and d.IsHoliday = 0

order by

d.[date] DESC

)

go

BTW, which version of SS are you using?

AMB

|||

Hi Hunckback!

You gave me 2 good answers! Thanks...

I will think the better for my case!!

Thanks!!

|||

Customized function that works perfectly for variable holidays!

Code Snippet

ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))

ReturnsDateTime

as

Begin

IfExists(Select 1 From Holidays Where HolidayDate =Dateadd(DD,-1,@.CurrentDate)AND HV_City=@.City)

return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)

Else

If @.NumberOfDays = 0

return @.CurrentDate

Else

return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)

returnnull

End

Now, imagine if I have a table for fixed holidays, and the holidays will be repeated for all the years but in our table we have only

1998-12-25

It means, for each year the month/day 12-25 is a holiday...

how can I do it?

Thanks!

|||

Is there any spl flag then use the following query..

Code Snippet

If Exists(Select 1 From Holidays

Where (

(HolidayDate = Dateadd(DD,-1,@.CurrentDate) And RepeateFlag=0)

or

(Month(HolidayDate)=Month(@.CurrentDate) And Year(HolidayDate) = Year(@.CurrentDate)

And RepeateFlag=1)

) AND HV_City=@.City)

Return dbo.WorkingDateSubtract(@.NumberOfDays, DateAdd(DD,-1,@.CurrentDate),@.City)

|||

Why the use of RepeateFlag?

Thanks!

|||So, you mean to say all the holidays are common across all the year. If not how you will know from your table, this holiday is for all the year, this for only current year?|||

I changed the function to:

Code Snippet

ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))

ReturnsDateTime

as

Begin

IfExists(Select 1 From dbo.VariableHolidays

Where(

HolidayDate =Dateadd(DD,-1,@.CurrentDate)

OR((Month(HolidayDate)=Month(@.CurrentDate)AndYear(HolidayDate)=Year(@.CurrentDate)))

)

AND Cities_ShortName=@.City)

Return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)

Else

If @.NumberOfDays = 0

return @.CurrentDate

Else

return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)

returnnull

End

Does not work... :-(

If I have the date 1998-06-26 as holiday does not recognize date 2007-06-26 as holiday. and if I have the record 2007-06-26 in holiday table, does not recognize to.

But this statment works for all the dates for variableholidays:

Code Snippet

ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))

ReturnsDateTime

as

Begin

IfExists(Select 1 From dbo.VariableHolidays

Where HolidayDate =Dateadd(DD,-1,@.CurrentDate)

AND Cities_ShortName=@.City

)

return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)

Else

If @.NumberOfDays = 0

return @.CurrentDate

Else

return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)

returnnull

End

I was think to create 2 diferent tables, one for fixedHolidays and other for variableHolidays, but if I can do it in only one table, would be better!!

Other funtionality that is missing and I didn't told you before, and is to get only workdays... I tried the statment below but the result still return saturday and sunday... :-(

Code Snippet

ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))

ReturnsDateTime

as

Begin

IfExists(Select 1 From dbo.VariableHolidays

Where HolidayDate =Dateadd(DD,-1,@.CurrentDate)

AND Cities_ShortName=@.City

AND(DATEPART(DW,HolidayDate)=2

ORDATEPART(DW,HolidayDate)=3

ORDATEPART(DW,HolidayDate)=4

ORDATEPART(DW,HolidayDate)=5

ORDATEPART(DW,HolidayDate)=6

))

return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)

Else

If @.NumberOfDays = 0

return @.CurrentDate

Else

return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)

returnnull

End

How can I do it?

Thanks for all!!!

|||

Ok.. Things are getting complicated now rite

Lets finish this now...

Code Snippet

Create Function [dbo].[WorkingDateSubtract]

(

@.NumberOfDays int,

@.CurrentDate Datetime,

@.City char(10)

)

Returns DateTime

as

Begin

If --Varibale Holidays

Exists(Select 1 From dbo.VariableHolidays

Where HolidayDate = @.CurrentDate

AND Cities_ShortName=@.City

)

Or --Fixed Holidays

Exists(Select 1 From dbo.FixedHolidays

Where Month(HolidayDate) = Month(@.CurrentDate)

And Day(HolidayDate) = Day(@.CurrentDate)

AND Cities_ShortName=@.City

)

Or --Saturday & Sundays

DateName(dw,@.CurrentDate) in ('Sunday', 'Saturday')

return dbo.WorkingDateSubtract(@.NumberOfDays, DateAdd(DD,-1,@.CurrentDate),@.City)

Else

If @.NumberOfDays = 0

return @.CurrentDate

Else

return dbo.WorkingDateSubtract(@.NumberOfDays-1, DateAdd(DD,-1,@.CurrentDate), @.City)

return null

End

|||

Dear Friend,

I delete the data in the tables FixedHolidays and VariableHolidays, in order to check if the funtion not return sundays and saturdays...

… 03-07-2007 Tuesday 02-07-2007 Monday 01-07-2007 Sunday 30-06-2007 Saturday 29-06-2007 Friday ok 28-06-2007 Thrusday NOK 27-06-2007 Wendsday ok 26-06-2007 Tuesday ok 25-06-2007 Monday 24-06-2007 Sunday 23-06-2007 Saturday NOK 22-06-2007 Friday ok 21-06-2007 Thrusday 20-06-2007 Tuesday 19-06-2007 Wendsday 18-06-2007 Tuesday 17-06-2007 Monday 16-06-2007 Sunday 15-06-2007 Saturday …

I was looking for each row, and for almost the days is correct, but for the day 28-06-2007 returns the saturday 23-06-2007

The numberofDays is 3 days for all rows in the example...

What you think about it?

Thanks for your important support!

|||Yes You are correct. I edited the code on my previous post. It should work fine now...|||

Dear Manivannan,

The function recognize saturday/sunday, variable days, ans in the fixed holidays works but I edited:

Code Snippet

WhereMonth(HolidayDate)=Month(@.CurrentDate)

AndDay(HolidayDate)=Day(@.CurrentDate)

AND Cities_ShortName=@.City

I changed the YEAR to DAY in the function!

You were fantastic, You have here a friend!!!

If you need something from me, dont hesitate!

Regards and thanks!

|||

Dear Manivannan,

There is a bug... for the date 30-06-2007 that is a saturday... subtracting 3 days, the date returned is 26-06-2007 in spite of 27-06-2007...

The problem seems to be when the current date is a saturday/sunday... could you help me changing the function?

Thanks!!!!

|||

I thought the input always working day.

If you have the above requirement then we can't do it on the recursive method..

Here I edited the code..

Code Snippet

Create Function [dbo].[WorkingDateSubtract]

(

@.NumberOfDays int,

@.CurrentDate Datetime,

@.City char(10)

)

Returns DateTime

as

Begin

Declare @.Scope as Int;

Set @.Scope = 0;

While @.NumberOfDays <> 0

Begin

If --Varibale Holidays

Exists(Select 1 From dbo.VariableHolidays

Where HolidayDate = @.CurrentDate

AND Cities_ShortName=@.City

)

Or --Fixed Holidays

Exists(Select 1 From dbo.FixedHolidays

Where Month(HolidayDate) = Month(@.CurrentDate)

And Day(HolidayDate) = Day(@.CurrentDate)

AND Cities_ShortName=@.City

)

Or --Saturday & Sundays

DateName(dw,@.CurrentDate) in ('Sunday', 'Saturday')

Select @.NumberOfDays = Case When @.Scope=0 Then -1 Else 0 End + @.NumberOfDays,

@.CurrentDate = DateAdd(DD,-1,@.CurrentDate),

@.Scope = @.Scope +1

Else

If @.NumberOfDays = 0

Select @.CurrentDate = @.CurrentDate

Else

Select @.NumberOfDays = @.NumberOfDays-1,

@.CurrentDate = DateAdd(DD,-1,@.CurrentDate),

@.Scope = @.Scope +1

End

return @.CurrentDate

End

Please clarify me, the following all the dates will return '2007-06-27' as output. Is it correct?

Select [dbo].[WorkingDateSubtract](3,'2007-07-02','')

Select [dbo].[WorkingDateSubtract](3,'2007-07-01','')

Select [dbo].[WorkingDateSubtract](3,'2007-06-30','')

Suppose, if your requirement expect output ='2007-06-28' for input = '2007-07-01' then change the following line

(on the first IF condition)

Code Snippet

Select @.NumberOfDays = Case When @.Scope=0 Then -1 Else 0 End + @.NumberOfDays,

@.CurrentDate = DateAdd(DD,-1,@.CurrentDate),

@.Scope = @.Scope + Case When @.Scope= 0 Then 0 Else 1 End

datepart

I am trying to use datepart to determine what row in a table a users
hiredate is closest to current system date
example
JOE was hired in Mar 01 2000
I need to get his payrate based off months experience
<12 months
<24 months
<60 months
<120 months
<200 months
Thanks
mike
something like below.
select hiredate, monthsrow from payee, payrategroup where
hiredate,
getdate(),
ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
+
ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
monthsrowHi
CREATE TABLE #Test
(
empl INT NOT NULL PRIMARY KEY,
hiredate DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'20060101')
INSERT INTO #Test VALUES (2,'20060101')
INSERT INTO #Test VALUES (3,'20060409')
INSERT INTO #Test VALUES (4,'20060110')
INSERT INTO #Test VALUES (5,'20060112')
INSERT INTO #Test VALUES (6,'20060120')
INSERT INTO #Test VALUES (7,'20060108')
INSERT INTO #Test VALUES (8,'20060103')
DECLARE @.dt DATETIME
SET @.dt ='20060115' --desired date
SELECT TOP 1 WITH TIES *
FROM #Test WHERE hiredate>'20050101' AND hiredate < DATEADD(day,1,@.dt)
ORDER BY hiredate DESC
<ciojr@.yahoo.com> wrote in message
news:1144550863.151230.197030@.t31g2000cwb.googlegroups.com...
>I am trying to use datepart to determine what row in a table a users
> hiredate is closest to current system date
> example
> JOE was hired in Mar 01 2000
> I need to get his payrate based off months experience
> <12 months
> <24 months
> <60 months
> <120 months
> <200 months
> Thanks
> mike
> something like below.
> select hiredate, monthsrow from payee, payrategroup where
> hiredate,
> getdate(),
> ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
> +
> ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
> monthsrow
>|||Hi Mike,
Can you give the ddls and the expected output. The question seems to be
a bit confusing.|||not what I am looking for.

datediff comparison to previous row in select statement

In a table I have information of a vehicle's status with an appropriate
date, what I am trying to achieve is to display the number of days
between two dates of two different statuses. Problem is that the same
scenario can happen on more than one occasion and there is no data
linking 2 statuses together. The query ran is
select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
datetime],20)
from vehicle v
left join [vehicle status history] vsh on v.[vehicle serial no_] =
vsh.[vehicle serial no_]
and vsh.status IN('BOOKING-IN','DESPATCHED')
where v.[reg no_]='R3RTF'
order by 3
reg no_ status
-- -- --
R3RTF BOOKING-IN 2005-01-11 08:22
R3RTF DESPATCHED 2005-02-03 18:34
R3RTF BOOKING-IN 2005-02-04 09:58
R3RTF DESPATCHED 2005-02-04 14:52
R3RTF BOOKING-IN 2005-04-05 14:33
R3RTF DESPATCHED 2005-06-01 17:37
Looking at these results what I need is the date difference between the
BOOKING-IN and DESPATCHED dates. From the result set above the 3
datediff (for days) values would be 23, 0 & 57.
This is all part of a bigger result set and ideally I would like the
sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
I am struggling to get this in a select statement, I have sampled using
cursors but I'm not sure this is the way to go. Using a SELECT
statement would be ideal.
Any help would be much appreciated.You can get the time a vehicle was booked in at a certain occasion with:
SELECT vd.[vehicle serial no_], vd.[from datetime], MIN(DATEDIFF(dd,
vb.[from datetime], vd.[from datetime])
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
If you want to have the sum of the times a vehicle spend booked in, you can
SUM over the previous query:
SELECT [vehicle serial no_], SUM(days_spend)
FROM (
SELECT vd.[vehicle serial no_], MIN(DATEDIFF(dd, vb.[from datetime],
vd.[from datetime]) AS days_spend
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
) ds
GROUP BY [vehicle serial no_]
(everything untested)
Jacco Schalkwijk
SQL Server MVP
"robz8701" <robz8701@.hotmail.com> wrote in message
news:1128070087.999572.326490@.g49g2000cwa.googlegroups.com...
> In a table I have information of a vehicle's status with an appropriate
> date, what I am trying to achieve is to display the number of days
> between two dates of two different statuses. Problem is that the same
> scenario can happen on more than one occasion and there is no data
> linking 2 statuses together. The query ran is
> select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
> datetime],20)
> from vehicle v
> left join [vehicle status history] vsh on v.[vehicle serial no_] =
> vsh.[vehicle serial no_]
> and vsh.status IN('BOOKING-IN','DESPATCHED')
> where v.[reg no_]='R3RTF'
> order by 3
> reg no_ status
> -- -- --
> R3RTF BOOKING-IN 2005-01-11 08:22
> R3RTF DESPATCHED 2005-02-03 18:34
> R3RTF BOOKING-IN 2005-02-04 09:58
> R3RTF DESPATCHED 2005-02-04 14:52
> R3RTF BOOKING-IN 2005-04-05 14:33
> R3RTF DESPATCHED 2005-06-01 17:37
> Looking at these results what I need is the date difference between the
> BOOKING-IN and DESPATCHED dates. From the result set above the 3
> datediff (for days) values would be 23, 0 & 57.
> This is all part of a bigger result set and ideally I would like the
> sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
> I am struggling to get this in a select statement, I have sampled using
> cursors but I'm not sure this is the way to go. Using a SELECT
> statement would be ideal.
> Any help would be much appreciated.
>