Date Range Parameter Help

Crystal Reports XI-

I have entered a date range parameter into my report to pick a range for Appointment Dates. When I choose a start date of 01/01/2007 and an end date of 01/31/2007, my report has all of January's appointments, but also has the last appointment of the previous month (in this case December). The same happens if I pick a date range from Feb 1st to the 28th. I get all of Feb appointments, but also the last day in January.

Am I missing something? Or has this happened to anyone else?Are you wanting the dates between your 2 date paramenters, and if so did you reference your date ranges in the record selection?

{table.appointmentdate} In {?StartDate} To {EndDate}

This will give you everything between the 2 dates.

GJ

Date Range for Previous Week

How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!

Code Snippet

CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]

(@.Classification varchar(50))

AS

BEGIN

SELECT RC_STAT.dbo.Brand_Dimension.Report_Level,

RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],

RC_STAT.dbo.Brand_Dimension.Item_Merged,

Qry_Sales_Group.Region_Key,

Qry_Sales_Group.Region,

Qry_Out_Of_Stock.product_structure_level,

Qry_Out_Of_Stock.product_entity_code,

Qry_Out_Of_Stock.cycle_day,

Qry_Out_Of_Stock.customer_code,

Qry_Out_Of_Stock.description,

Qry_Sales_Group.Code,

Qry_Sales_Group.SR_Name,

Qry_Sales_Group.Name AS Territory_Name,

Qry_Out_Of_Stock.Store_Name,

Qry_Out_Of_Stock.time_log,

Qry_Out_Of_Stock.out_of_stock,

Period_Code

FROM RC_STAT.dbo.Brand_Dimension INNER JOIN

dbo.Qry_Out_Of_Stock ON

RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN

dbo.Qry_Sales_Group ON

dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS

Where Classification=@.Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) =

case

when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3

else datediff(dd,0,GetDate()) - 1

end

END

SET NOCOUNT OFF

Here it is,

Code Snippet

CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY]

(@.Classification varchar(50))

AS

BEGIN

SELECT

RC_STAT.dbo.Brand_Dimension.Report_Level,

RC_STAT.dbo.Brand_Dimension.[Cat vs Dog],

RC_STAT.dbo.Brand_Dimension.Item_Merged,

Qry_Sales_Group.Region_Key,

Qry_Sales_Group.Region,

Qry_Out_Of_Stock.product_structure_level,

Qry_Out_Of_Stock.product_entity_code,

Qry_Out_Of_Stock.cycle_day,

Qry_Out_Of_Stock.customer_code,

Qry_Out_Of_Stock.description,

Qry_Sales_Group.Code,

Qry_Sales_Group.SR_Name,

Qry_Sales_Group.Name AS Territory_Name,

Qry_Out_Of_Stock.Store_Name,

Qry_Out_Of_Stock.time_log,

Qry_Out_Of_Stock.out_of_stock,

Period_Code

FROM

RC_STAT.dbo.Brand_Dimension

INNER JOIN dbo.Qry_Out_Of_Stock

ON RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS

Where

Classification=@.Classification

And dbo.Qry_Out_Of_Stock.time_log >= dateadd(dd,-6,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))), --Monday

And dbo.Qry_Out_Of_Stock.time_log < dateadd(dd,-1,dateadd(dd,1-datepart(w,getdate()),convert(varchar,getdate(),101))) -- Less than Saturday

END

SET NOCOUNT OFF

|||Thanks!! now how would i do this for periods. I do have a period field, but its a Char so i cant really do the (-1) . What would be a good aproach for this, is there a way i can have the timelog=period, each period has a date.|||What do you meant "Period", can you provide some sample data (both input & expected output)

|||

I have a "Period_code" Field in the stored procedure. The field is a char. And looks like this 200708 (for period 8). Should i convert this number to an INT and then do a -1? for the where clause. And do a trim, to just grab the last 2 digits.

I want to get all the records for the previous period. So if today is period 200709 , i want period 200708 etc etc. I hope this helps.

Also how would i get it to realized that today=period 200709 or whatever period it is, on the date the report is run. Im trying to find a way to link these.

Date range ...

Hi,
I have to count the number of invoices between two dates (starting date and ending date).
I have a time dimension, and a another with the two dates.
I've created a measure :

Code Snippet

CREATE MEMBER [CTNAV].[Measures].[Nb]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date ].CurrentMember.MemberValue >=
[Time].[Date].CurrentMember.MemberValue
)
)

The problem is that if I display all the dates, each measure is at 0 but the total is ok (>0).

Sorry for my english.

Thanks for your help.

Date question, Can I get Q1, of 2006 begin and end dates?

I would like to get the first and last day of a quarter by passing in todays
date.
I can get the quarter were in with
select (DATEPART(Quarter,GetDate()))
Now I want to get the day the quarter started (Min day of the quarter) and
the Last day of the quarter (Max day of the quarter) i.e. 4/1/06 and 6/30/06
.
Tall MikeUse a calendar table.
http://www.aspfaq.com/2519
"TallMike" <TallMike@.discussions.microsoft.com> wrote in message
news:62668B64-9559-46FA-98CB-03E781C4B55F@.microsoft.com...
>I would like to get the first and last day of a quarter by passing in
>todays
> date.
> I can get the quarter were in with
> select (DATEPART(Quarter,GetDate()))
> Now I want to get the day the quarter started (Min day of the quarter)
> and
> the Last day of the quarter (Max day of the quarter) i.e. 4/1/06 and
> 6/30/06.
>
> --
> Tall Mike|||Either of these sring expressions can easilty be converted to a
datetime. I figured that they would be more easily understood without
wrapping them in that.
select convert(char(4),datepart(year,getdate())
) +
CASE DATEPART(Quarter,GetDate())
WHEN 1 THEN '0101'
WHEN 2 THEN '0401'
WHEN 3 THEN '0701'
WHEN 4 THEN '1001'
END as QuarterStartString,
convert(char(4),datepart(year,getdate())
) +
CASE DATEPART(Quarter,GetDate())
WHEN 1 THEN '0331'
WHEN 2 THEN '0630'
WHEN 3 THEN '0930'
WHEN 4 THEN '1231'
END as QuarterEndString
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 13:36:02 -0700, TallMike
<TallMike@.discussions.microsoft.com> wrote:
>I would like to get the first and last day of a quarter by passing in today
s
>date.
>I can get the quarter were in with
>select (DATEPART(Quarter,GetDate()))
>Now I want to get the day the quarter started (Min day of the quarter) and
>the Last day of the quarter (Max day of the quarter) i.e. 4/1/06 and 6/30/06.[/colo
r]|||Check this out, it works
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as 'Last Q ended'
select DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0) as 'Last Q started'
Tall Mike
"Roy Harvey" wrote:

> Either of these sring expressions can easilty be converted to a
> datetime. I figured that they would be more easily understood without
> wrapping them in that.
> select convert(char(4),datepart(year,getdate())
) +
> CASE DATEPART(Quarter,GetDate())
> WHEN 1 THEN '0101'
> WHEN 2 THEN '0401'
> WHEN 3 THEN '0701'
> WHEN 4 THEN '1001'
> END as QuarterStartString,
> convert(char(4),datepart(year,getdate())
) +
> CASE DATEPART(Quarter,GetDate())
> WHEN 1 THEN '0331'
> WHEN 2 THEN '0630'
> WHEN 3 THEN '0930'
> WHEN 4 THEN '1231'
> END as QuarterEndString
> Roy Harvey
> Beacon Falls, CT
> On Wed, 3 May 2006 13:36:02 -0700, TallMike
> <TallMike@.discussions.microsoft.com> wrote:
>
>|||On Wed, 3 May 2006 15:25:02 -0700, TallMike
<TallMike@.discussions.microsoft.com> wrote:

>Check this out, it works
>select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as 'Last Q ended'
>select DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0) as 'Last Q started'
Very nice! It came pretty close, but the Last Q ended was April 1,
not March 31.
But it didn't need much work.
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)-1 as 'Last Q ended'
select DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0) as 'Last Q started'
Roy Harvey
Beacon Falls, CT

Date query question!

Hi,

I've a problem with making a query. I need to get out a result where a date
for example 25-02-05 is in a table.
The problem is that the dates in the table are old like 25-02-03. So is
there a sollution where I only need the day and month to get the result?

Thanks,
Fred"news.wanadoo.nl" <fabfreddy@.zonnet.nl> wrote in message
news:421f7fda$0$32252$afc38c87@.news.wanadoo.nl...
> Hi,
> I've a problem with making a query. I need to get out a result where a
> date for example 25-02-05 is in a table.
> The problem is that the dates in the table are old like 25-02-03. So is
> there a sollution where I only need the day and month to get the result?
> Thanks,
> Fred

select *
from dbo.MyTable
where right(convert(char(8), datecol, 112), 4) = '0225'

select *
from dbo.MyTable
where day(datecol) = 25 and month(datecol) = 2

These will return multiple rows, of course, if you have 20030225, 20020225
etc in your table - I don't know if that's what you want or not. Also check
out DATEPART() in Books Online.

Simonsql

Date query problem!

Hi,
I've a problem with making a query. I need to get out a result where a date
for example 25-02-05 is in a table.
The problem is that the dates in the table are old like 25-02-03. So is
there a sollution where I only need the day and month to get the result?
Thanks,
FredIf I inderstood, you want to see if there is a row for a specific month and
day.
declare @.d datetime
set @.d = '20050225'
select * from t where month(colA) = month(@.d) and day(colA) = day(@.d)
go
AMB
"news.wanadoo.nl" wrote:

> Hi,
> I've a problem with making a query. I need to get out a result where a dat
e
> for example 25-02-05 is in a table.
> The problem is that the dates in the table are old like 25-02-03. So is
> there a sollution where I only need the day and month to get the result?
> Thanks,
> Fred
>
>|||news.wanadoo.nl wrote:
> Hi,
> I've a problem with making a query. I need to get out a result where a dat
e
> for example 25-02-05 is in a table.
> The problem is that the dates in the table are old like 25-02-03. So is
> there a sollution where I only need the day and month to get the result?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
By "old" do you mean the dates are stored as strings in the format "25
02-03," or, do you mean the dates are in the past, in the year 2003,
instead of the year 2005?
Assuming (a lot) that the dates are stored as DateTime data types:
SELECT *
FROM table_name
WHERE date_column = '20050225'
Normally, a query's WHERE clause filters which records are selected from
a table. Read the Books On Line (BOL) articles on the WHERE clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQh+P24echKqOuFEgEQLt9gCeOV3ZFXBu+JAp
CbMNi+I5rS4w104AoMBB
kpwTK1OPe8MZP/XAsyfoSRDb
=KstK
--END PGP SIGNATURE--|||These dates are old and are in the DateTime type.
So what I need is an WHERE clause that can select also the old dates. With a
input date that is in 2005! Is this possible?
Thx
"MGFoster" <me@.privacy.com> wrote in message
news:rdMTd.5740$873.3054@.newsread3.news.pas.earthlink.net...
> news.wanadoo.nl wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> By "old" do you mean the dates are stored as strings in the format "25
> 02-03," or, do you mean the dates are in the past, in the year 2003,
> instead of the year 2005?
> Assuming (a lot) that the dates are stored as DateTime data types:
> SELECT *
> FROM table_name
> WHERE date_column = '20050225'
> Normally, a query's WHERE clause filters which records are selected from
> a table. Read the Books On Line (BOL) articles on the WHERE clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQh+P24echKqOuFEgEQLt9gCeOV3ZFXBu+JAp
CbMNi+I5rS4w104AoMBB
> kpwTK1OPe8MZP/XAsyfoSRDb
> =KstK
> --END PGP SIGNATURE--|||Yes. See my post below. The WHERE clause shows how to select for the
date 25 Feb 2005. The date format is YYYYMMDD.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
news.wanadoo.nl wrote:
> These dates are old and are in the DateTime type.
> So what I need is an WHERE clause that can select also the old dates. With
a
> input date that is in 2005! Is this possible?
> Thx
> "MGFoster" <me@.privacy.com> wrote in message
> news:rdMTd.5740$873.3054@.newsread3.news.pas.earthlink.net...
>
>
>sql

Date Query

How to write a query which will return list of all the dates from say 3 days from current date and also next 3 days from current date..

eg say

Output should be something like this:

Date
----
02/09/2002
02/10/2002
02/11/2002
02/12/2002 <-- Today's date
02/13/2002
02/14/2002
02/15/2002

Any comments or suggestions are welcome.

Thanks! in advance.Does SQL Server have a DUAL table, or something similar? In Oracle, I'd do:

SELECT SYSDATE (getDate() in SQL Server)
FROM DUAL
WHERE getDate() BETWEEN DateAdd ( 'd', -3, getDate() ) AND DateAdd ( 'd', 3, getDate() )

Obviously, that's a mix of Oracle and SQL Server syntax, but it should be fairly easy to tell which is which. The key is the DUAL table equivalent. I don't know if SQL Server has something like that. You might have to tweak the syntax a little, too. I rarely use the DateAdd function, myself, so I could be off a little.|||select convert(varchar(8),dateadd(dd,a.i, getdate()),112)
from
(
select i = -3
union select -2
union select -1
union select 0
union select 1
union select 2
union select 3
) as a

or using a temp table
create table #a (d datetime)
declare @.d

select @.d = convert(varchar(8),dateadd(dd,-3, getdate()),112)
while @.d <= convert(varchar(8),dateadd(dd,3, getdate()),112)
begin
insert #a select @.d
select @.d = dateadd(dd,1,@.d)
end
select * from #a
drop table #asql

date problems (AGAIN!)

I am trying to use asp to add/edit dates in a sql server db.

I can add a date fine using: -

INSERT INTO news(news_title, news_date, news_intro, news_text)
VALUES('The title of the news',
'25/06/2004',
'This is the introduction to the news article',
'this is the text for the news')

however when i try to update using: -

update news set news_date='23/05/2004' WHERE news_id = 11

I get the following error: -

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

the date column is of the datatype datetime
I also have the same problem if i use the same sql directly in query analyzer

Any help HUGELY appreciated, cheers

DanHi,

The way you pass the date format to the SQL should be in mm/dd/yyyy.
Unless you specifially use the Convert Function, you will have to use this in this format. The reason is because there is a default configuration (I dont know where it is :-) ) that takes the date in this format.

HTH
Thanks
Shankar

Date Problem

In our database we have dates in the format:

05/02/2007 10:39:32
05/02/2007 12:33:22
06/02/2007 20:19:32
07/02/2007 13:33:32
08/02/2007 17:35:42

We are using a .net calendar from visual studio to allow users select a date. The date is returned in the format 05/02/2007. No time is included.

We wish to select all data from, for instance the 05/02/2007 from our MSSQL server. We have a query to select based on a date in this format but it does not work.

SELECT Time
FROM DB
WHERE (Time = '05/02/2007');

We get no responses in query analyser for this and incorrect ones when we use parenthesis.

you will need to account for the time component of your dates when writing your queries. Date selections are virtually always done as a range selection

for example:

SELECT TimeFROM DBWHERE (Time >='05/02/2007')AND (Time <'05/03/2007') ;

|||

Hi,

I ran this query

SELECT

StartTime, EndTime

FROM

aspnet_SortedTrip

WHERE

(device='353863842373')AND(StartTime>'2007-02-01'And StartTime<'2007-02-08')

This is the output

2007-02-01 20:32:03.000 2007-02-01 21:51:00.000
2007-02-03 12:33:56.000 2007-02-03 14:05:00.000
2007-02-05 10:46:01.000 2007-02-05 10:50:33.000
2007-02-05 11:45:35.000 2007-02-05 11:55:34.000
2007-02-06 11:39:41.000 2007-02-06 11:56:45.000
2007-02-06 20:00:29.000 2007-02-06 21:05:18.000
2007-02-07 19:25:34.000 2007-02-07 19:33:34.000

Should it retrieve data for the 1st of March even though i had StartTime > '2007-02-01'?

Is that correct?

|||

I do not see any values for the 1st of March in your output. ?

The output appears to be valid - you have records ranging from 02/01/2007 through the end of the daya on 02/07/2007 (mm/DD/yyyy)

Note:, you sould use a >= instead of the > .
If a record was added at exaclty midnight on the morning of 02/07/2007, your query would miss it.

WHERE(device ='353863842373')AND (StartTime >='2007-02-01'And StartTime <'2007-02-08')

|||

Re: Date Problem

ReplyQuoteFavoritesContact

Hi,

I ran this query

SELECT

StartTime, EndTime

FROM

aspnet_SortedTrip

WHERE

(device='353863842373')AND(StartTime>'2007-02-01'And StartTime<'2007-02-08')

This is the output

2007-02-01 20:32:03.000 2007-02-01 21:51:00.000
2007-02-03 12:33:56.000 2007-02-03 14:05:00.000
2007-02-05 10:46:01.000 2007-02-05 10:50:33.000
2007-02-05 11:45:35.000 2007-02-05 11:55:34.000
2007-02-06 11:39:41.000 2007-02-06 11:56:45.000
2007-02-06 20:00:29.000 2007-02-06 21:05:18.000
2007-02-07 19:25:34.000 2007-02-07 19:33:34.000

The format of the above output is yyyy-mm-dd followed by time.

This is the structure of the data in the table. Is this correct?

So that why i asked should it retireve data for the 1st of March even thoough i ran the query as Time > 1st of March and Time < 8th of March

|||

Does any body know how i can increment a Date Value?

Im retrieving a date value from a button click on a calender.

Id like to be able to increment the date by 1 day.

Is this possible to do?

Should the date be in a datetime format in order to increment the day value?

|||

So that why i asked should it retireve data for the 1st of March even thoough i ran the query as Time > 1st of March and Time < 8th of March

your query was from the 1st of February through the 8th of February...not March

|||

Sorry i only realised that now.

But should it be retriving data from the 1st of February even though the query states > 1st of Feb?

Is it possible to increment the day value in date

Example:

1st of Feb incremented to the 2nd of Feb

|||

There are two important date functions you can play with: DateAdd and DateDiff.

To add one day, try this:

SELECT

DATEADD(day,1,GetDate()),GetDate()

You can search these two functions and you will find enough information to handle your case.

|||

Hi,

Thanks for all the posts just figured out our problem.

Thanks.

|||

emersondub:

But should it be retriving data from the 1st of February even though the query states > 1st of Feb?

although using > will select almost all datetimes from Feb 1st, if a record were added on Feb 1st with a zero time value (midnight), then that record would not be selected even though it was on Feb 1st. To insude you dont drop these records, alway use >=

Date problem

I have a simple query which returns records between specific dates however
it seems I have to use the mm/dd/yy method however im in the UK and we use
dd/mm/yy how can I get sql server to use the right one?
--
Regards
Gary Howlett
Systems Developer
www.rainbowgrp.co.ukJust ust the "yyyymmdd" format, it is independent of your current settings.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:QPI_a.3395$z7.558306@.wards.force9.net...
> I have a simple query which returns records between specific dates however
> it seems I have to use the mm/dd/yy method however im in the UK and we use
> dd/mm/yy how can I get sql server to use the right one?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||Hi Gary,
The problem here is not SQL Server but the machine SQL
runs on.
You will need to change the local setting for that machine
to England (i.e. Pounds, dd/mm/yyyy ect) on that machine.
If its a case that you are remove connecting then you can
follow Dejan sugestion or use the 'style' option on a cast
or convert function (for further details see bol).
Peter
>--Original Message--
>I have a simple query which returns records between
specific dates however
>it seems I have to use the mm/dd/yy method however im in
the UK and we use
>dd/mm/yy how can I get sql server to use the right one?
>--
>Regards
>Gary Howlett
>Systems Developer
>www.rainbowgrp.co.uk
>
>.
>|||Use the ISO standard YYYYMMDD format when passing dates to SQL Server, then
there is no confusion no matter where you put your server, who messes with
regional settings, or what SET DATEFORMAT or SET LANGUAGE options have been
configured.
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:QPI_a.3395$z7.558306@.wards.force9.net...
> I have a simple query which returns records between specific dates however
> it seems I have to use the mm/dd/yy method however im in the UK and we use
> dd/mm/yy how can I get sql server to use the right one?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>

Date Picker in US Format after SP2

Updated SQL 2005 to SP2 and Reporting Services Date Picker now has dates defaulting to US format (MM/DD/YYYY) instead of our previous English-Australian (DD/MM/YYYY). Both the clients and server are set to English Australian regional settings in the O/S and the reports are composed for English Australian language. Reports have correct format in Visual Studio development and re-deploying them had no effect. Everything was fine under SP1 - not sure what is going on. Any ideas?

I have had so much trouble with date fields that I now keep them as text fields in Reporting Services and pass then through to SQL Server as VarChars(). Using three letters for the month (Jan, Feb, Mar etc) is the best way to may sure that SQL Server gets it right. I always set a default in the format "12 Apr 07 13:00" so the use sees what format is required. This means not using the date control but in my applications we time as well as date and the date control doesn't do this anyway.

The problem seems to be that there are many places to set the "country" or "language" etc.

Places that I can think of are:-

1. Control Panel/Regonal and Language Settings.

2. SQL Server using Set Language (or Set DateFormat). I believe that this affects the current session and so must be used in every report query.

3. Intenet Explorer, which is relevant when accessing Report Server.

I am not sure which SP I have but the patch level is 9.1.2153.

|||After doing some more analysis; I have found that if a datetime parameter prompt is placed after two parameters that cascade, then the correct date format (in our case English-Australian DD/MM/YYYY) is used in the Date Picker (probably due to the language settings in the client and report). Otherwise you get the US Date format (MM/DD/YYYY). This means that if you have datetime parameters on their own or even with drop downs, multi-selects etc.. then you could be in for trouble. I could be mistaken but this seems like a bug in SQL 2005 SP2.|||The problem has now been narrowed down to the Reporting Services Sharepoint web parts: rswebparts.cab (v2.0). Reports run with the correct date picker format from the report server but are incorrect if they are run from the SharePoint report viewer web part. I believe these web parts were not updated in SP2 (see http://msdn2.microsoft.com/en-us/library/ms159772.aspx)|||We get this same problem outside of sharepoint after sp2. If you go directly to the reportserver (http://yourserver/reportserver) and navigate to the report you may find the same. This problem has surfaced after the sp2 upgrade - strange thing is that is does not occur if we run the report via report manager - I expect why the sharepoint is showing the problem is because it is making a direct call to the reportserver.

Date Part of the date time

I have data in a smalldatetime column but the dates are
mixed. Some in the '3/7/04 11:47:00 AM' form and some
in '3/7/04' (This is when I open the table from EM).
When I select from the Query Analyzer I get
'2004-01-08 13:35:00'
My question is How can I select only the date part of it
(Not the time part) to compare with another column ?
Thnaks for any help.......select convert (varchar, <date>,101) returns the date in mm/dd/yyyy format.
It comes out as a string. That can easily be compared against another date.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...

quote:

---

> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

---

|||SELECT CONVERT(SMALLDATETIME, CONVERT(CHAR(8), column, 112)) FROM table
You can wrap this in a function for slightly cleaner code and slightly more
overhead. Depending on the size of your table, of course. I have a
function called dbo.getDayFloor() that handles this for me.
Note: I always convert to the smaller of the two datetime datatypes if I'm
only dealing with day boundaries and not concerned about time.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...

quote:

---

> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

---

sql

Date Part of the date time

I have data in a smalldatetime column but the dates are
mixed. Some in the '3/7/04 11:47:00 AM' form and some
in '3/7/04' (This is when I open the table from EM).
When I select from the Query Analyzer I get
'2004-01-08 13:35:00'
My question is How can I select only the date part of it
(Not the time part) to compare with another column ?
Thnaks for any help.......select convert (varchar, <date>,101) returns the date in mm/dd/yyyy format.
It comes out as a string. That can easily be compared against another date.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...
> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......|||SELECT CONVERT(SMALLDATETIME, CONVERT(CHAR(8), column, 112)) FROM table
You can wrap this in a function for slightly cleaner code and slightly more
overhead. Depending on the size of your table, of course. I have a
function called dbo.getDayFloor() that handles this for me.
Note: I always convert to the smaller of the two datetime datatypes if I'm
only dealing with day boundaries and not concerned about time.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...
> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

Date output to flat file

I am using a simple input from an SQL data base where I have 4 dates defined as type D. I am writing to a flat file with the fileds defined with any available date format and the output on the flat file comes out as "mm/dd/yy 00:00". I'd like to just have the date portion with no time. The input does not have a time on it so I understand the 00:00 as the value. It seems that I shouldn't have to do any extra work as it is date to date. I've seen the gyrations for a date from the SQL database when it is a character field, but that's not the issue here.

Thanks!

how does your sql data looks like? mm/dd/yyyy hh:mis?

if you are using a data flow task, you may want to add data conversion transformation to it, to convert to your required format.

|||In your source, use a SQL statement to retrieve the records. For the four date fields, use the following code:

CONVERT(varchar(20), yourDateField, 101)|||The fields are not character - they are defined as date fields in the SQL table.|||It is shown on the table layout as a "D" type with 0 length.|||That is why Phil asked you to convert into varchar with your required format in sql command.

Thanks|||

JLBSYS wrote:

The fields are not character - they are defined as date fields in the SQL table.

Yeah, I know. This converts them to character strings. Try it in management studio to see what it does.

OR your other option is to take what you've currently got built and add a derived column to create a new column that casts the date/time fields to DT_DBDATE fields.|||

This is what I did since I had to work a few other fields also. I was just wondering why you had to go through gyrations to get an input date to an output date. Since you can select the format on the ADVANCE option of the output connection manager for the Flat file. Changing the format does not change the output.

Thanks everyone for quick response!

Date math, don't know if this is possible please help

I have a set of dates they are non contiguous. Like for instance
--StartDate--End_date--Title--
1/05/2003 2/15/2004 Superintendant
2/30/2005 5/25/2006 Manager
12/1/1987 1/01/2003 Superintendant
what I need to do is grab the fields that are superintendant only (which is
easy enough) but show would I look at those date ranges (without a function)
and add up the total years + days + months
something like this
xx years yy months and zz days?
--
Message posted via http://www.sqlmonster.comOn Aug 18, 6:26 pm, "scrocker via SQLMonster.com" <u36708@.uwe> wrote:
> I have a set of dates they are non contiguous. Like for instance
> --StartDate--End_date--Title--
> 1/05/2003 2/15/2004 Superintendant
> 2/30/2005 5/25/2006 Manager
> 12/1/1987 1/01/2003 Superintendant
> what I need to do is grab the fields that are superintendant only (which is
> easy enough) but show would I look at those date ranges (without a function)
> and add up the total years + days + months
> something like this
> xx years yy months and zz days?
> --
> Message posted viahttp://www.sqlmonster.com
It's a bit tricky to do this with 2 dates; however, using more than
that, you will most likely have to do the tabulation in the stored
procedure/query that is sourcing the report. For two dates, you might
be able to use something like:
=CStr(DateDiff("y", Fields!StartDate.Value, Fields!EndDate.Value)) + "
years " +
CStr(DateDiff("m", DateAdd("y", DateDiff("y", Fields!StartDate.Value,
Fields!EndDate.Value), Fields!StartDate.Value), Fields!EndDate.Value))
+ " months and " +
CStr(DateDiff("d", DateAdd("m", DateAdd("y", DateDiff("y", Fields!
StartDate.Value, Fields!EndDate.Value), Fields!StartDate.Value),
DateDiff("m", DateAdd("y", DateDiff("y", Fields!StartDate.Value,
Fields!EndDate.Value), Fields!StartDate.Value), Fields!EndDate.Value),
Fields!StartDate.Value)), Fields!StartDate.Value)) + " days"
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Date loop

Please Help!
I am looking for a way to automatically loop thru dates starting 2006-01-01
thru 2006-12-01. The field I am trying to query is called endts.
I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
12:00:00PM'
I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
Could someone please post sample code on how this can be done. If it is
possible.
Thanks in Advance.
BRyou can try this one:
declare @.enddate datetime
declare @.loopdate datetime
set @.loopdate = '2006-01-01 12:00:00AM'
set @.enddate = '2006-12-01 12:00:00PM'
while @.loopdate <= @.enddate
begin
select @.loopdate -- Put your code here
set @.loopdate = @.loopdate+1
end
"BR" wrote:

> Please Help!
> I am looking for a way to automatically loop thru dates starting 2006-01-0
1
> thru 2006-12-01. The field I am trying to query is called endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>
>|||You may find that this, and similar date related tasks, are best done using
a Calendar Table.
See:
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BR" <so> wrote in message news:%23Cq1lVuHHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Please Help!
> I am looking for a way to automatically loop thru dates starting
> 2006-01-01 thru 2006-12-01. The field I am trying to query is called
> endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>|||BR
Can you post DDL+ sample data + an expected result?
SELECT <columns> FROM Table WHERE dtcolumn >= '20060101 12:00:00' AND
dtcolumn <DATEADD(d,1,'20060101' )
"BR" <so> wrote in message news:%23Cq1lVuHHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Please Help!
> I am looking for a way to automatically loop thru dates starting
> 2006-01-01 thru 2006-12-01. The field I am trying to query is called
> endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>

Date loop

Please Help!
I am looking for a way to automatically loop thru dates starting 2006-01-01
thru 2006-12-01. The field I am trying to query is called endts.
I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
12:00:00PM'
I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
Could someone please post sample code on how this can be done. If it is
possible.
Thanks in Advance.
BR
you can try this one:
declare @.enddate datetime
declare @.loopdate datetime
set @.loopdate = '2006-01-01 12:00:00AM'
set @.enddate = '2006-12-01 12:00:00PM'
while @.loopdate <= @.enddate
begin
select @.loopdate-- Put your code here
set @.loopdate = @.loopdate+1
end
"BR" wrote:

> Please Help!
> I am looking for a way to automatically loop thru dates starting 2006-01-01
> thru 2006-12-01. The field I am trying to query is called endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>
>
|||You may find that this, and similar date related tasks, are best done using
a Calendar Table.
See:
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BR" <so> wrote in message news:%23Cq1lVuHHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Please Help!
> I am looking for a way to automatically loop thru dates starting
> 2006-01-01 thru 2006-12-01. The field I am trying to query is called
> endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>
|||BR
Can you post DDL+ sample data + an expected result?
SELECT <columns> FROM Table WHERE dtcolumn >= '20060101 12:00:00' AND
dtcolumn <DATEADD(d,1,'20060101' )
"BR" <so> wrote in message news:%23Cq1lVuHHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Please Help!
> I am looking for a way to automatically loop thru dates starting
> 2006-01-01 thru 2006-12-01. The field I am trying to query is called
> endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>

Date loop

Please Help!
I am looking for a way to automatically loop thru dates starting 2006-01-01
thru 2006-12-01. The field I am trying to query is called endts.
I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
12:00:00PM'
I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
Could someone please post sample code on how this can be done. If it is
possible.
Thanks in Advance.
BRYou may find that this, and similar date related tasks, are best done using
a Calendar Table.
See:
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BR" <so> wrote in message news:%23Cq1lVuHHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Please Help!
> I am looking for a way to automatically loop thru dates starting
> 2006-01-01 thru 2006-12-01. The field I am trying to query is called
> endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>|||BR
Can you post DDL+ sample data + an expected result?
SELECT <columns> FROM Table WHERE dtcolumn >= '20060101 12:00:00' AND
dtcolumn <DATEADD(d,1,'20060101' )
"BR" <so> wrote in message news:%23Cq1lVuHHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Please Help!
> I am looking for a way to automatically loop thru dates starting
> 2006-01-01 thru 2006-12-01. The field I am trying to query is called
> endts.
> I need to know where endts between '2006-01-01 12:00:00AM' and '2006-01-01
> 12:00:00PM'
> I need to loop thru this everyday from 2006-01-01 thru 2006-12-01.
> Could someone please post sample code on how this can be done. If it is
> possible.
> Thanks in Advance.
> BR
>sql

Date Issue!

I am storing dates in SQL Server 2000 (Using ASP).
The date is getting validated against the Ymd format, by using SET DATEFORMA
T.
In the Enterprise Manager - the date is displayed in local format (dmY).
When i run a store procedure manually it is returned as Ymd.
When i execute stored procedure from asp and load data into a recordset it i
s
displayed in mdY Format.
This is obviously quite confusing. Can anyone give me some insight into what
is happening. How i can retrieve a date in one format (Ymd).Hi
Seems u have to format date using FORMAT Function
renjith
"AJ" wrote:

> I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET DATEFOR
MAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into wh
at
> is happening. How i can retrieve a date in one format (Ymd).|||I dont know which Function you mean with FORMAT, but here is another option
for that. A good pratice for me is to get the data back in a very common
format and to format it at the client side. The settings the date and time
is formatted depends on some settings concerning the client, the User
connnction to SQL Server (with its special user localized settings) or the
server settings. Perhaps you should read here a little bit further:
http://www.karaszi.com/SQLServer/in...p#OutputFormats
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A2A764AE-4BD0-4B0F-B5FF-A9FF40B6060F@.microsoft.com...
> Hi
> Seems u have to format date using FORMAT Function
> renjith
> "AJ" wrote:
>|||Hi
since AJ is using ASP , there is format function in VB for formatting data
so tht he can give as Format(DateColumn, "Ymd").
Renjith
"Jens Sü?meyer" wrote:

> I don′t know which Function you mean with FORMAT, but here is another opt
ion
> for that. A good pratice for me is to get the data back in a very common
> format and to format it at the client side. The settings the date and time
> is formatted depends on some settings concerning the client, the User
> connnction to SQL Server (with it′s special user localized settings) or t
he
> server settings. Perhaps you should read here a little bit further:
> http://www.karaszi.com/SQLServer/in...p#OutputFormats
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A2A764AE-4BD0-4B0F-B5FF-A9FF40B6060F@.microsoft.com...
>
>|||OK, that makes sense, i just thought you mean th non-existing function
FORMAT for TSQL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6F1D14DE-AB4F-4CE9-B7BE-6FDC5C1772EC@.microsoft.com...
> Hi
> since AJ is using ASP , there is format function in VB for formatting data
> so tht he can give as Format(DateColumn, "Ymd").
> Renjith
>
> "Jens Smeyer" wrote:
>|||The regional settings on your computer influence how the date is presented
in Enterprise Manager. So if the regional settings are British Enterprise
Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
influenced by the regional settings, and will always display dates in
yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.
However, how SQL Server interprets the dateformat of strings depends on the
settings for your login in SQL Server. In Enterprise Manager, look under
<Server>\Security\Logins and look at the Default Language for your username
(or BUILTIN\Administrators if you are a Windows administrator on your local
machine).
To avoid problems with dates as strings, use the formats that are always
interpreted the same by SQL Server independent of any settings:
yyyymmdd and
yyyy-mm-ddThh:mm:ss
Jacco Schalkwijk
SQL Server MVP
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:FCADD866-6062-4125-9338-4C378973487B@.microsoft.com...
>I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET
> DATEFORMAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
> is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into
> what
> is happening. How i can retrieve a date in one format (Ymd).|||> Query Analyzer isn't
> influenced by the regional settings
... unless you check Tools, Options, Connections, "Use regional settings wh
en displaying ... dates
and times". :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eZ5Z1z3XFHA.3040@.TK2MSFTNGP14.phx.gbl...
> The regional settings on your computer influence how the date is presented
> in Enterprise Manager. So if the regional settings are British Enterprise
> Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
> influenced by the regional settings, and will always display dates in
> yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.
> However, how SQL Server interprets the dateformat of strings depends on th
e
> settings for your login in SQL Server. In Enterprise Manager, look under
> <Server>\Security\Logins and look at the Default Language for your usernam
e
> (or BUILTIN\Administrators if you are a Windows administrator on your loca
l
> machine).
> To avoid problems with dates as strings, use the formats that are always
> interpreted the same by SQL Server independent of any settings:
> yyyymmdd and
> yyyy-mm-ddThh:mm:ss
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AJ" <AJ@.discussions.microsoft.com> wrote in message
> news:FCADD866-6062-4125-9338-4C378973487B@.microsoft.com...
>|||You need to change the query in all enviornments to use the convert date
standard formats.
select convert(nvarchar,getdate(),101)
Which returns in mm/dd/yyyy format.
05/23/2005
Refer to books online and search for CAST and CONVERT as search criteria and
you will find the list of output standards.
"AJ" wrote:

> I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET DATEFOR
MAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into wh
at
> is happening. How i can retrieve a date in one format (Ymd).

date Intervals

Is that like some interval function for dates, like i want to group my data in intervals of 15 minutes and 30 minutes. Is there such a function in T-SQLNot really. But you can use divide to achive this. See below for illustration
declare
@.datetable table
(
date_col datetime
)
insert into @.datetable(date_col)
select dateadd(minute, n1 + n2, '2006-01-01 01:00')
from
(
select 0 as n1 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9
) as num1
cross join
(
select 0 as n2 union all
select 10 union all select 20 union all select 30 union all
select 40 union all select 50 union all select 60
) as num2

select date_col, datediff(minute, 0, date_col) / 15 as timeslot -- 15 mins interval
from @.datetable
order by timeslot, date_col|||Slight variation: Use MOD operatore instead of divisor, and convert back to datetime value -
select date_col,
dateadd(minute, datediff(minute, 0, date_col) % 15, 0)
from @.datetable
order by timeslot, date_col|||You can also experiment with datepart() to get parts of the date.|||This will group the time
01:00, 01:15, 01:30, 01:45 as one group,
01:01, 01:16, 01:31, 01:46 as another group.

Well, It really depends on what jcwc888 means by "intervals of 15 minutes" :)