Dates and Differences between Query Analyzer and Enterprise Manager

Hey Everyone,

I am just starting to learn T-SQL and came across something that puzzled me when looking at dates.

If I enter this:
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) AS Expr1

In Enterprise Manager I get this output
12/30/2002 - which is what I assumed that I should get

In Query Analyzer I get this output
2002-12-30 00:00:00.000 - which I did not expect

Does anyone know why there would be a difference and if so, how do I get Query Analyzer to format as 12/31/2002?

Thanks alot,
BrentTry only: SELECT CONVERT(varchar, GETDATE(), 101) AS Expr1|||The style parameter is used when converting to a character type from a datetime - so when you converted it back to a datetime in query analyzer it showed you what you asked for. This is the true format - em will jack with the format but the reality is different. em will see all zeroes for time as no time and only displays slashes (no dashes).

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 problem with week extraction (T-SQL)

I'm using datepart combined with a count aggregate to count the number of
ws in a certain time period. Problem is, my employer starts each w on
Saturday. The T-SQL version of Datepart does not support a StartOfW
parameter. This defect is screwing up my reports.
Does anyone have a workaround?
Thanks,
Randall ArnoldLook up SET DATEFIRST in BOL to control the first day of the w for date
functions
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
> I'm using datepart combined with a count aggregate to count the number of
> ws in a certain time period. Problem is, my employer starts each w
> on Saturday. The T-SQL version of Datepart does not support a StartOfW
> parameter. This defect is screwing up my reports.
> Does anyone have a workaround?
> Thanks,
> Randall Arnold
>|||Another alternative is to use a calendar table, whcih gives you the
flexibility of using multiple calendars associated with the same date.
Stu|||But be aware that SQL Server doesn't calculate w number the way that the
majority of the world
does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl..
.
> Look up SET DATEFIRST in BOL to control the first day of the w for date
functions
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
>|||Apparently not.
First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a stored procedure didn't result in an error, but it
had no effect, either. No matter what value I set Datefirst to, the w is
still calculated wrong. For my purposes, 4/2/2005 needs to show as w 15
(first day of w = Saturday), but it always shows as w 14.
I don't see how a date lookup table will solve this... so, any other ideas?
This shortcoming in T-SQL is producing invalid results in my datasets...
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
> But be aware that SQL Server doesn't calculate w number the way that
> the majority of the world does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||> I don't see how a date lookup table will solve this...
You have a table with one row per day. One of the columns in this table is t
he w correct number.
Or, read in Books Online about the ISOW function, install and use that in
stead of DATEPART().
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
> Apparently not.
> First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a
> stored procedure didn't result in an error, but it had no effect, either.
No matter what value I
> set Datefirst to, the w is still calculated wrong. For my purposes, 4/
2/2005 needs to show as
> w 15 (first day of w = Saturday), but it always shows as w 14.
> I don't see how a date lookup table will solve this... so, any other ideas
? This shortcoming in
> T-SQL is producing invalid results in my datasets...
> Randall Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
>|||Ok, thanks Tibor. I should have realized how the date (w) lookup would
work (I've done a similar thing for Periods), my bad. I'm just already
linking to so many tables on this query it's become a nightmare (due to bad
database design by the original dba). I'll get familiar with with ISOWEEK
and if that doesn't work, I have a Period lookup table already and I'll just
add a w column to it.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>|||I decided just to add the W column to my existing Period table. It meant
changing several queries, as well as more manual maintenance, but it works.
Thanks again.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>

DateDiff Trivial Problem

Hy Friends,
I'm having a new annoying problem wiht T-SQL.
I need to remove 10 Seconds from a Datetime Value.
Somethin like This:
This date
07/24/2003 14:25:02
I want it like this
07/24/2003 14:24:52
Suggestions?select DATEADD(ss, -10, date_column) from table