How to make this DATE format

im having some trouble getting this specifik date format:

dd-MM-yyyy hh:mm

where the time is in 24 hour format...

Date and time formatting seems a lot harder in sql server 2005 than in access

This:

SELECT convert( varchar(16), getdate(), 120 )

Returns:

-

2007-05-07 13:04

Refer to Books Online, Topic: 'Cast and Convert' for the 'style' (120, above) values.

How to make the order by date fine enough in SQL

hi,

I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.

Thanks very much.

Then you're trying to do a select order by datedesc, and group by date, within each group you wanna order by timeasc, right? If so you’re trying to do sorting within grouped records. In SQL2005 you can useRANK Functions, for example:

SELECT*,RANK()OVER(PARTITIONBYCONVERT(VARCHAR,CDATE,102)

ORDERBYCONVERT(VARCHAR,CDATE,108))AS RANK

FROM testP

ORDERBYCONVERT(VARCHAR,CDATE,102)DESC

|||Thanks!

how to make report parameter value change dynamic when run time?

Dear all,

i have 2 parameter [startDate] [endDate].
startDate will basic on the day run report to get the start date from system(follow the store procedure) as below.

CREATE PROCEDURE [dbo].[CFSRep_spGetLastWeekMonDate]
AS
BEGIN
DECLARE @.dtStart DATETIME
-- SET @.dtStart = GetDate() --Set Date to Todays Date
--Set Start Date to 6 days before. If its a Sunday it will get this weeks Monday Date, else any day before will get the past weeks Monday Date
SELECT @.dtStart = dateadd(dd, -6, Getdate())
WHILE (datepart(dw, @.dtStart) != 2) --While its not Day 2 (Monday)
BEGIN
SELECT @.dtStart = dateadd(dd, -1, @.dtStart) --Set the Start Date to a day before
END

SET @.dtStart = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.dtStart))) --Set the time to 00:00:00 AM

SELECT @.dtStart as LastWeekFirstDay
--

END
GO


endDate will get the date after the startDate is process. let say the answer as below.
SELECT DATEADD(dd, 4, @.startDate) as endDate

when i get the startDate = 30th Jan 2006, then the endDate = 3rd Feb 2006

then when i preview report, and i change the parameter of the date on start date to 23rd Jan 2006 but the endDate still the same 3rd Feb 2006.

anyway for me to refesh the endDate to 27th Jan 2006?

Hi Terence,

first of all you can use visual basic functions like Today etc. to get the startdate. In case of that you must not go to the database.

then you can try the following steps:

1. define 2 parameters (startdate + enddate)

2. in the enddate-dataset define a parameter @.startDate and set it to =Parameters!StartDate.Value.

3. make your 'SELECT DATEADD(dd, 4, @.startDate) as endDate' statement.

Now the reportservice should set in the endate-parameter the startdate-param +4 days.

hope this will work for you

sql

How to List information by date

Hi,

I want to track the lifecycle of a request(requestno), the time it was submitted to accepted/denied.

The information is captured as :

Audit

auditid
adminid
auditdate
requestno
taskid

auditid adminid auditdate requestno taskid
1 1 12/06/2007 1 1
2 3 13/06/2007 2 1
3 12 12/06/2007 3 1
4 2 13/06/2007 1 2
5 2 16/06/2007 1 3
6 4 14/06/2007 2 2
7 4 7/06/2007 2 4

Task
taskid
taskname

taskid taskname
1 Submitted
2 InProgress
3 Accepted
4 Denied
5 Transferred

I want a query that will list down the following. There should be only one row for each request :

RequestNo DtSubmit DtProgress DtAccepted DtDenied Admin
1 12/06/2007 13/06/2007 16/06/2007 null 2
2 13/06/2007 14/06/2007 null 17/06/2007 4
3 12/06/2007 null null null 12

Thanks,

Vidya.

here it is,

Code Snippet

Create Table #audit (

[auditid] int ,

[adminid] int ,

[auditdate] datetime ,

[requestno] int ,

[taskid] int

);

SET DATEFORMAT DMY

Insert Into #audit Values('1','1','12/06/2007','1','1');

Insert Into #audit Values('2','3','13/06/2007','2','1');

Insert Into #audit Values('3','12','12/06/2007','3','1');

Insert Into #audit Values('4','2','13/06/2007','1','2');

Insert Into #audit Values('5','2','16/06/2007','1','3');

Insert Into #audit Values('6','4','14/06/2007','2','2');

Insert Into #audit Values('7','4','7/06/2007','2','4');

Create Table #task (

[taskid] int ,

[taskname] Varchar(100)

);

Insert Into #task Values('1','Submitted');

Insert Into #task Values('2','InProgress');

Insert Into #task Values('3','Accepted');

Insert Into #task Values('4','Denied');

Insert Into #task Values('5','Transferred');

Select

RequestNo

,Max(Case When T.[taskid]=1 Then [auditdate] End) DtSubmit

,Max(Case When T.[taskid]=2 Then [auditdate] End) DtInProgress

,Max(Case When T.[taskid]=3 Then [auditdate] End) DtAccepted

,Max(Case When T.[taskid]=4 Then [auditdate] End) DtDenied

,Max(Case When T.[taskid]=5 Then [auditdate] End) DtTransferred

,Max(Adminid)

From

#audit A

Join #task T On A.[taskid] = T.[taskid]

Group By

RequestNo

|||

Bingo!!

Thanks.

How to limit the time Dimension to 24 months

MSAS Time Dimension Question

Well here it goes...I am trying to create a date dimension that will only pull in 24 months worth of date information.

Example: I want the end date to be the current date and the start date to be 24 months prior...as the current date changes (End date) the Start date will also change to reflect the date changes. I'm thinking something similar to a rolling 12 or 24 month time dimension.

NOTE: My time Dimension needs to be similar to that of a Bank account (NOT Accumulative)

Ex:

May 1 =$ 1.50

May 2 =$ 1.42

ect..

May 31 =$0.65

1-1 relationship on Date to Amount

NO rollup within the date dimension. I would love to have folder structures built into the Date Dimension Year/Quarter/Month but not rollup...more of an external rollup.

Just a side note...the date field within my SQL DB has Ten years worth of date fields but I want to limit it to only 2 years.

Anyone have any good ideas?

lochew

In this case, you should create your dimenison manually using the dimension wizard (Server Time dimenison). You can specify exactly the starting and end date of your dimension.

If you want to avoid the rollup you can siply associate the NONE additive aggregation funciton to the measure.

|||

Thanks tdhers,

Is this option available within MSAS/SQL 2000? I haven't seen the (Server Time Dimension) but I will take another look. I'll also look into the NONE additive aggregation funciton.

Thanks again

|||No it is AS2005 only

How to limit the time Dimension to 24 months

MSAS Time Dimension Question

Well here it goes...I am trying to create a date dimension that will only pull in 24 months worth of date information.

Example: I want the end date to be the current date and the start date to be 24 months prior...as the current date changes (End date) the Start date will also change to reflect the date changes. I'm thinking something similar to a rolling 12 or 24 month time dimension.

NOTE: My time Dimension needs to be similar to that of a Bank account (NOT Accumulative)

Ex:

May 1 =$ 1.50

May 2 =$ 1.42

ect..

May 31 =$0.65

1-1 relationship on Date to Amount

NO rollup within the date dimension. I would love to have folder structures built into the Date Dimension Year/Quarter/Month but not rollup...more of an external rollup.

Just a side note...the date field within my SQL DB has Ten years worth of date fields but I want to limit it to only 2 years.

Anyone have any good ideas?

lochew

In this case, you should create your dimenison manually using the dimension wizard (Server Time dimenison). You can specify exactly the starting and end date of your dimension.

If you want to avoid the rollup you can siply associate the NONE additive aggregation funciton to the measure.

|||

Thanks tdhers,

Is this option available within MSAS/SQL 2000? I haven't seen the (Server Time Dimension) but I will take another look. I'll also look into the NONE additive aggregation funciton.

Thanks again

|||No it is AS2005 only

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!
|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!
|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This link might be helpful also
http://www.aspfaq.com/2312
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This link might be helpful also
http://www.aspfaq.com/2312
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the search like this?

there is a column called "date" with the data format like this:
2003-11-12 10:59:22.997
I can limit the search by using the sql statement like this:
select id from logtable where date like '%2003%'
but how to limit the search to be able to display 2003-11-12 only?
thanks very much!!http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!|||Hi,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue as MVP Tibor's
link is excellent! We appreciate your patience and look forward to hearing
from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||This link might be helpful also
http://www.aspfaq.com/2312
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Figo" <abc@.abc.com> wrote in message news:40E0E1E5.BC07FD68@.abc.com...
> there is a column called "date" with the data format like this:
> 2003-11-12 10:59:22.997
> I can limit the search by using the sql statement like this:
> select id from logtable where date like '%2003%'
> but how to limit the search to be able to display 2003-11-12 only?
> thanks very much!!

How to limit the MDX dataset for the last six months

I have the following MDX query and I'd like to put in another condition that the "[Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS" has to be GREATER than OR EQUAL TO last six months. Eg. using today date as 21-Nov-2006 and the dataset should only include the records from 21-May-2006 onward.

SELECT NON EMPTY { [Measures].[Pharmacy DW Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Patient Hospital and Drug])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks

It depends how you build your True Prescription Date attribute. If we will assume, that it is rebiult daily, and the last attribute member is today's date (i.e. no dates go to the future), then it would be something like

Lag([Account Period].[True Prescription Date].[All True Prescription Dates].LastChild, 182):[Account Period].[True Prescription Date].[All True Prescription Dates].LastChild)

If the rules are more complex, then it is probably best to build named set inside MDX Script which will resolve to the last 6 months worth of dates, and use it instead in the queries.

How to know what schema was modified

Hello,

Is there a way in MSSQL server to find all the objects in the database
based on the modified date rather than the created date.

Thanks in advance
Kumuunfortunately, sqlserver does not store such info. so, you can't.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"kumu" <forlist2001@.yahoo.com> wrote in message
news:a44abd39.0311191320.6e962c15@.posting.google.c om...
> Hello,
> Is there a way in MSSQL server to find all the objects in the database
> based on the modified date rather than the created date.
> Thanks in advance
> Kumu

how to know time of insertion of a row

Hi
Is there a way to know time of insertion of a row without using,
timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives me
the date of insertion of a row.
--
Thanks in Advance
Regards
SuparichituduNo, there isn't
However , you are be able to create a column with GEDATE() as a DEFAULT
CONSTRAINT.
CREATE TABLE Test (col1 INT,col2 DATETIME DEFAULT GETDATE())
GO
INSERT INTO Test (col1) VALUES (100)
GO
SELECT * FROM Test
"Suparichithudu" <Suparichithudu@.discussions.microsoft.com> wrote in message
news:42420237-454F-49EE-B756-21326EAADE0C@.microsoft.com...
> Hi
> Is there a way to know time of insertion of a row without using,
> timestamp,currentdate function etc. Isthere any DBCC COMMAND which gives
> me
> the date of insertion of a row.
> --
> Thanks in Advance
> Regards
> Suparichitudu
>|||if you're in the unenviable situation where you need to retrospectively
find this out, one thing that might be able to help you is if you can
get hold of the transaction logs over the period that you know it was
inserted. However that would require you to not be using a simple
recovery model.
There are some tools you can get free trials of which will query a
logfile, but I'm not sure of their reliability.

How to know latest update date of each stored procedure ?

on SQL Server 2000

They show only Create date

but I need know update date

because I install my system on customer's site and solve problem on customer site

and I can't bring all stored procedure back to my office and restore all stored

because of my database have two projects.

Please Help me....

You may need:

select * from [Information_Schema].routines where ROUTINE_TYPE='PROCEDURE'

|||

Oh

It's cool

THANK YOU VERY MUCH......

:D