date range query not returning anticipated results

Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>

date question

in my database i stored information on what time the purchaed were made something like this...

ItemNo|dateofPurchased
*********************
0001 | 12/08/2003
0002 | 1/24/2002
0003 | 5/18/2001

......

if i wish to get the itemno that were purchased before year 2002 how do i do it by sql statements?
thanks...Originally posted by HjF_84
in my database i stored information on what time the purchaed were made something like this...

ItemNo|dateofPurchased
*********************
0001 | 12/08/2003
0002 | 1/24/2002
0003 | 5/18/2001

......

if i wish to get the itemno that were purchased before year 2002 how do i do it by sql statements?
thanks...
The answer depends on your DBMS. For Oracle you would say:

select itemno
from xxx
where dateofpurchased < TO_DATE('01-JAN-2002','DD-MON-YYYY');

But TO_DATE is Oracle-specific.|||i using sql server2000..., so anyone know how to retreive it...?

Date question

I currently get a date in the default format in my stored procedure. Up until now converting the date to type 101 was acceptable. Now they want the date to read something like Wednesday, November 8, 2006 or November 8, 2006. How do I do this convert in a single select statement?

I found the types that add the time but I have to make sure that the time isn't included for merge reasons.

Help

Jeff

Mookey:

Are you looking for something like this:

declare @.myDate datetime set @.myDate = getdate()

select dateName (dw, @.myDate) + ', ' +
dateName (month, @.myDate) + ' ' +
convert (varchar (2), datepart (dd, @.myDate)) + ', ' +
convert (varchar (4), year(@.myDate)) as [Date Name String]

--
-- S A M P L E O U T P U T :
--

-- Date Name String
-- -
-- Wednesday, November 8, 2006

|||Formatting of dates should be preferable done in the frontent not in the backend.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Mookey wrote:

I currently get a date in the default format in my stored procedure. Up until now converting the date to type 101 was acceptable. Now they want the date to read something like Wednesday, November 8, 2006 or November 8, 2006. How do I do this convert in a single select statement?

I found the types that add the time but I have to make sure that the time isn't included for merge reasons.

Help

Jeff

SELECT CAST(DATENAME(MONTH, GETDATE()) AS varchar(15)) + ' ' + CAST(DATEPART(DAY, GETDATE()) AS varchar(2)) + ', ' + CAST(DATEPART(YEAR, GETDATE()) AS varchar(4))

Adamus

|||

I second what Jens states (unless you are doing a one time data conversion using SQL, then follow the other ideas :).

Leave the formatting of dates (and any values) to the presentation layer. There should be some common object that they reuse for all date values that are returned in the native SQL Server date datatype, which is not actually formatted any way. SSMS uses a standard format to display dates, which is the best format for passing dates, but clearly not what the average person want to see in the UI. Then the formatting of dates can be tied to the machines formatting, or controlled via a configuration that all users use.

date query when date field is char and mmyy format

Hi All,
I have a field which stored date as char datatype in mmyy format.
Acutally, I have inherited this and there is no way I can change the
design of the database.
Now, I needed to run a qeury between two months (from December 2005 to
January 2006).
My query was:
select * from table1 where claim_date between '1205' and 0106'
This did not give me any records whereas, I know there are records for
the month of December (claim_date : 1205)
I understand that 0106 is smaller than 1205 and that is the main reason
that I am not getting any records. Because if I change it to
select * from table1 where claim_date between '0106' and '1205', then
it gives me the results I want. I feel this is not right.
My question is how can I handle this problem in the query, so that I
can really use the date range as claim_date between '1205' and '0106'.
CREATE TABLE [dbo].[table1] (
[Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Insert table1
select 'D124', '1205', 'A'
union all
select 'D243', '0106', 'A'
Thanks a million in advance.
Best regards,
MamunWithout changing the table structure and storing the right data in the right
format, don't expect this to have sizzling performance.
SELECT * FROM
(
SELECT
*,
claim_date_calc = '20'+RIGHT(claim_date,2)+LEFT(claim_date
,2)+'01'
FROM
table1
WHERE
ISDATE('20'+RIGHT(claim_date,2)+LEFT(cla
im_date,2)+'01') = 1
)
x
WHERE CONVERT(SMALLDATETIME, claim_date_calc) BETWEEN '20051201' AND
'20060101';
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1139340637.499729.6330@.f14g2000cwb.googlegroups.com...
> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>|||where substring(Claim_Date,3,2)+substring(Clai
m_Date,1,2)
between '0512' and '0601'|||Both solutions worked great. I was not looking at the performance but
to get the results.
Thanks a ton to both of you.
best regards,
mamun|||Another approach would be to convert the values for claim_date into honest
datetimes, and compare to other datetime values
SELECT Claim_Number, Claim_Date, Prime_ABC
FROM table1
WHERE
CONVERT(datetime, RIGHT(Claim_Date,2) + LEFT(Claim_Date,2) + '01', 12)
BETWEEN '20051201' AND '20060101'
With all respect to Alexander, using BETWEEN with character datatypes gives
me the heebee jeebees.
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> I have a field which stored date as char datatype in mmyy format.
> Acutally, I have inherited this and there is no way I can change the
> design of the database.
> Now, I needed to run a qeury between two months (from December 2005 to
> January 2006).
> My query was:
> select * from table1 where claim_date between '1205' and 0106'
> This did not give me any records whereas, I know there are records for
> the month of December (claim_date : 1205)
> I understand that 0106 is smaller than 1205 and that is the main reason
> that I am not getting any records. Because if I change it to
> select * from table1 where claim_date between '0106' and '1205', then
> it gives me the results I want. I feel this is not right.
> My question is how can I handle this problem in the query, so that I
> can really use the date range as claim_date between '1205' and '0106'.
> CREATE TABLE [dbo].[table1] (
> [Claim_Number] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Claim_Date] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Prime_ABC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> Insert table1
> select 'D124', '1205', 'A'
> union all
> select 'D243', '0106', 'A'
> Thanks a million in advance.
> Best regards,
> Mamun
>

Date Problem!

A column in a SQL Server7 DB table has the datatype smalldatetime. All
the records in this column are stored in 'm/d/yyyy' format like
6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc....
Assume that two of the records stores today's date i.e. 9/20/2005 (20th
September 2005). When I execute the following 2 queries in QA
SELECT * FROM tblRecords WHERE Date1='9/20/2005'
SELECT * FROM tblRecords WHERE Date1='20050920'
both the queries retrieve the two records as expected but when I issue
the following query
SELECT * FROM tblRecords WHERE Date1=GETDATE()
no records get retrieved! Why so?
Thanks,
ArpanBecause GETDATE() includes time information. Did you run SELECT GETDATE()
and see that it does not return exactly 20050920?
If you want all rows from today, the most efficient way is probably:
WHERE Date1 >= 0 + DATEDIFF(DAY, 0, GETDATE())
AND Date1 < 1 + DATEDIFF(DAY, 0, GETDATE())
or
DECLARE @.today SMALLDATETIME
SET @.today = 0 + DATEDIFF(DAY, 0, GETDATE())
SELECT
...
WHERE Date1 >= @.today
AND Date1 < (@.today + 1)
(In addition to using an index on date1, this will also allow you to ignore
any time values that might slip into the values in date1.)
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1127189510.583307.210400@.f14g2000cwb.googlegroups.com...
>A column in a SQL Server7 DB table has the datatype smalldatetime. All
> the records in this column are stored in 'm/d/yyyy' format like
> 6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc....
> Assume that two of the records stores today's date i.e. 9/20/2005 (20th
> September 2005). When I execute the following 2 queries in QA
> SELECT * FROM tblRecords WHERE Date1='9/20/2005'
> SELECT * FROM tblRecords WHERE Date1='20050920'
> both the queries retrieve the two records as expected but when I issue
> the following query
> SELECT * FROM tblRecords WHERE Date1=GETDATE()
> no records get retrieved! Why so?
> Thanks,
> Arpan
>|||Thanks, Aaron, for your help. Good suggestion :-)
Thanks once again,
Regards,
Arpan

Date problem

I have a table that has two datetime columns and the following stored
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks

DATE PARAMETER-- Simple Question

Hi,
I am using the following simple stored procedure to dispaly data between 2
date ranges.
CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
(
@.manucode varchar(50),
@.brand varchar(50),
@.StartDate datetime,
@.EndDate datetime
)
AS
SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
FROM ITEMHIST INNER JOIN
ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND( ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
GO
Now the prblem is that when I enterd start date as 1 Sept 2004 and end date
as 1Aug 2005, then there is no data displayed. I know the problem is with th
e
last 2 "AND" clauses of my stored procedure. Please help. I want to display
data between the 2 date ranges.
Thanks
--
pmudAND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
Did you mean EndDate here, and not StartDate, on the last line? Anyway, I'm
not sure that breaking down a datetime into month and year to calculate a
range is very wise. If you're interested in blocking by months only, have
you considered adding a column to ITEMHIST that represents the month and
year combined, e.g. 20050101, 20050201, etc. This makes querying by date
ranges a true date range query, instead of separating the components of the
date and assuming that the range will always have a starting month and year
and then the ending date is starting month - 1 and ending year + 1.
A
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO,
> ITEMS.DESCRIPT,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end
> date
> as 1Aug 2005, then there is no data displayed. I know the problem is with
> the
> last 2 "AND" clauses of my stored procedure. Please help. I want to
> display
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Try,
...
WHERE
(ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND (ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
between (Year(@.StartDate ) * 100) + Month(@.StartDate)
AND (Year(@.EndDate) * 100) + Month(@.EndDate)
If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
do not expect sql server to perform an index s in these indexes. Using
those columns in an expression, limit then to be considered search arguments
.
AMB
"pmud" wrote:

> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIP
T,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end dat
e
> as 1Aug 2005, then there is no data displayed. I know the problem is with
the
> last 2 "AND" clauses of my stored procedure. Please help. I want to displa
y
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Adding a computed column as Aaron mentioned would seem to be a much better
long term solution that what you are doing now. That said, if you are
unable to alter the schema, give this a try. Replace the last two AND
conditions with the following:
cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
varchar) as datetime) between @.StartDate and @.EndDate
--Brian
(Please reply to the newsgroups only.)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> Did you mean EndDate here, and not StartDate, on the last line? Anyway,
> I'm not sure that breaking down a datetime into month and year to
> calculate a range is very wise. If you're interested in blocking by
> months only, have you considered adding a column to ITEMHIST that
> represents the month and year combined, e.g. 20050101, 20050201, etc.
> This makes querying by date ranges a true date range query, instead of
> separating the components of the date and assuming that the range will
> always have a starting month and year and then the ending date is starting
> month - 1 and ending year + 1.
> A
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
>|||Hi ,
Yes, I meant @.EndDate in the last line. Is there any other way of doing
this. It is not necesaary to do month and year separately. i did it that way
bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
month) and FISCAL_YEAR fields.
Is there any way I can do it together rather than breaking it down...If not,
then can you please explain your menthod in detail..
Thansk for all your help..
--
pmud
"Alejandro Mesa" wrote:
> Try,
>
> ...
> WHERE
> (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND (ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
> between (Year(@.StartDate ) * 100) + Month(@.StartDate)
> AND (Year(@.EndDate) * 100) + Month(@.EndDate)
> If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
> do not expect sql server to perform an index s in these indexes. Using
> those columns in an expression, limit then to be considered search argumen
ts.
>
> AMB
> "pmud" wrote:
>|||Hi,
Actually I just now checked, Aaron was right, I by chance wrote @.StartDate
instaed of @.EndDate in the sp and after changing ti , it works. I apologize.
Though I would really like to underastand the enw methods you all told me.I
didnt quite understand them...Can you please explain your solutions...
I really appreciate all your help
--
pmud
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||It is hard for me to give you a good explanation because my englis is far
from good, but I am posting an example so you can get the idea.
I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
(FISCAL_YEAR * 100) + PERIOD
and the same with the start and end dates. Then I am selecting just where
this number is between the ones from start and end date.
create table t1 (
c1 int not null identity primary key,
c2 int not null check (c2 between 1900 and 3000),
c3 int not null check (c3 between 1 and 12)
)
go
insert into t1(c2, c3) values(2000, 1)
insert into t1(c2, c3) values(2000, 5)
insert into t1(c2, c3) values(2001, 6)
insert into t1(c2, c3) values(2002, 8)
insert into t1(c2, c3) values(2005, 7)
insert into t1(c2, c3) values(2005, 8)
go
declare @.sd datetime
declare @.ed datetime
set @.sd = '20000201'
set @.ed = '20050701'
select
c1,
c2,
c3,
(c2 * 100) + c3 as c4,
(year(@.sd) * 100) + month(@.sd) as c5,
(year(@.ed) * 100) + month(@.ed) as c6
from
t1
where
(c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
100) + month(@.ed)
order by
c2, c3
go
drop table t1
go
AMB
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||Hi Aljendro,
Thanks for talking the time and explaining it. I appreciate it. Your
explanation was very helpful and whatever little remaining doubt I have I
think when I will implement it myself, that will make it still clearer.
Thanks for ur help.
--
pmud
"Alejandro Mesa" wrote:
> It is hard for me to give you a good explanation because my englis is far
> from good, but I am posting an example so you can get the idea.
> I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
> (FISCAL_YEAR * 100) + PERIOD
> and the same with the start and end dates. Then I am selecting just where
> this number is between the ones from start and end date.
> create table t1 (
> c1 int not null identity primary key,
> c2 int not null check (c2 between 1900 and 3000),
> c3 int not null check (c3 between 1 and 12)
> )
> go
> insert into t1(c2, c3) values(2000, 1)
> insert into t1(c2, c3) values(2000, 5)
> insert into t1(c2, c3) values(2001, 6)
> insert into t1(c2, c3) values(2002, 8)
> insert into t1(c2, c3) values(2005, 7)
> insert into t1(c2, c3) values(2005, 8)
> go
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20000201'
> set @.ed = '20050701'
> select
> c1,
> c2,
> c3,
> (c2 * 100) + c3 as c4,
> (year(@.sd) * 100) + month(@.sd) as c5,
> (year(@.ed) * 100) + month(@.ed) as c6
> from
> t1
> where
> (c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
> 100) + month(@.ed)
> order by
> c2, c3
> go
> drop table t1
> go
>
> AMB
>
> "pmud" wrote:
>|||Hi Brian,
I used the cast statement as it is.. and it works...I am trying to
undersatnd how it exactly works though...
pmud
"Brian Lawton" wrote:

> Adding a computed column as Aaron mentioned would seem to be a much better
> long term solution that what you are doing now. That said, if you are
> unable to alter the schema, give this a try. Replace the last two AND
> conditions with the following:
> cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
> varchar) as datetime) between @.StartDate and @.EndDate
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
>
>|||Basically it just converts your Period and FiscalYear combination into a
date with the format mm/01/yyyy. As a datetime datatype, it then does the
comparison to the @.StartDate and @.EndDate via the BETWEEN.
--Brian
(Please reply to the newsgroups only.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:0AD179F0-3096-48B9-88D3-AD7C9F9BE88E@.microsoft.com...
> Hi Brian,
> I used the cast statement as it is.. and it works...I am trying to
> undersatnd how it exactly works though...
> --
> pmud
>
> "Brian Lawton" wrote:
>

Date parameter getting converted

I have my report's data source set to a stored procedure that I created which
takes 2 date parameters. If I run the dataset while in the Data Tab it works
fine but when I go to the preview window no data shows up. I ran a SQL
Profiler trace to figure out what was going on. Apparently in the Data Tab
the dates are being passed in just as typed but in the preview window the
dates get converted to extremely tiny decimals...9.677419354838709e-002. How
can I get these dates carried through properly? I even tried to make them
String datatype instead of DateTime, same results.It now appears that these same decimals are being passed in no matter what I
type in the date boxes...
"BrianW" wrote:
> I have my report's data source set to a stored procedure that I created which
> takes 2 date parameters. If I run the dataset while in the Data Tab it works
> fine but when I go to the preview window no data shows up. I ran a SQL
> Profiler trace to figure out what was going on. Apparently in the Data Tab
> the dates are being passed in just as typed but in the preview window the
> dates get converted to extremely tiny decimals...9.677419354838709e-002. How
> can I get these dates carried through properly? I even tried to make them
> String datatype instead of DateTime, same results.|||I figured it out. In the Parameters Tab of my dataset I thought I was
supposed to put default values in. Instead I put the expression of my Report
Parameters in and it worked. Wow, these message boards are great!
"BrianW" wrote:
> It now appears that these same decimals are being passed in no matter what I
> type in the date boxes...
> "BrianW" wrote:
> > I have my report's data source set to a stored procedure that I created which
> > takes 2 date parameters. If I run the dataset while in the Data Tab it works
> > fine but when I go to the preview window no data shows up. I ran a SQL
> > Profiler trace to figure out what was going on. Apparently in the Data Tab
> > the dates are being passed in just as typed but in the preview window the
> > dates get converted to extremely tiny decimals...9.677419354838709e-002. How
> > can I get these dates carried through properly? I even tried to make them
> > String datatype instead of DateTime, same results.

Date parameter

hi and thanx in advance
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?

If date format difference is true, it will give error at some point. So, the problem could be only with your stored procedure. Try executing the stored procedure from the management studio with the same parameter data and see if you get any records.

Shyam

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?
I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?|||Thanks! A lot of good information on your hyperlink.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manage
r
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on wha
t
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

Date formatting problem

I have a crystal report that is populated by the results of a SQL stored procedure. The data returned is grouped by year and this appears along the x axis of the chart. The problem is that it sees the years as numbers and changes them to 2,000 2,001 2,002 etc. I have tried defining them as dates in the stored proc but this makes no difference and the chart options >grid >group axis does not allow me to alter the numbers category to date as it is greyed out.

Does anyone know how I can display these dates correctly.

Thanks for any help in advance.Try formatting the field to show without the comma.

Example in CR 8.5 using RDC... Right-click on the field and click Format, there's a tab called Date/Time or Number or something like that.|||I am also having this problem, expect I have used a formula field to combine two date fields to give me a date range (February 15-20, 2004), but I am getting February 15.00-20.00, 2,004.00. I can't change the formatting since the formula field is a string. Any suggestions?|||Try changing the default Number Format. In CR 8.5 using RDC, right-click to bring up the menu, Click Designer, Default Settings. Click the Fields tab. Click the Number button. This will allow you to set the default format for the Crystal Reports Program, not just the report you're working on.

Date formats in SQL

I need to compare a date field in a WHERE clause and I'm finding it very
difficult to do so.
I have a date stored in tables as integers like so:
20060317
etc
I need to compare todays date (less a day) to get the data out of the
table...so my query looks like this:
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
However, the GetDate() statements between the then and else clauses return
the following:
2006-03-17 15:08:15.663
Now I just want the date to be yyyymmdd so I can compare. I have tried just
about everything I know and I cannot get the format to be yyyymmdd.
Thx
BillyBil, CAST your integer AS CHAR(8) il will workd fine.
A +
Billy a écrit :
> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SET @.DT_CURDATE = CAST(FLOOR(CAST(getdate() AS DECIMAL(15, 8))) AS DATETIME)
And then use @.DT_CURDATE instead of getdate()
Hope that helps
"Billy" wrote:

> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy|||On Mon, 20 Mar 2006 07:57:32 -0800, Billy wrote:

>I need to compare a date field in a WHERE clause and I'm finding it very
>difficult to do so.
>I have a date stored in tables as integers like so:
>20060317
Hi Billy,
Don't do this.
Change your DB. Store the date as a datetime or a smalldatetime. It'll
save you lots of headaches.
(snip)
>However, the GetDate() statements between the then and else clauses return
>the following:
>2006-03-17 15:08:15.663
>Now I just want the date to be yyyymmdd so I can compare. I have tried just
>about everything I know and I cannot get the format to be yyyymmdd.
Ugly kludge:
SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS int)
Hugo Kornelis, SQL Server MVP

Date format retrieval

Hello!
I have two dates each with the datetime format.
However in one, only the date portion is being stored and in the other
the time with the date of 1/1/1900 is being stored.
For eg. datefield1 = '2/5/2007 00:00:00'
datefield2 = '1/1/1900 09:35:00'
How can I get only the date part in 'Datefield1' and the time part in
'Datefield2'
the resultant should be a date format as I want to compare it with another
datetime field.
Thank you very much for your help!
-Me
Suggestion #1 is to store these values together. What are you gaining by
splitting them apart?
Suggestion #2 I'll demonstrate with local variables:
DECLARE
@.datefield1 SMALLDATETIME,
@.datefield2 SMALLDATETIME;
SELECT
@.datefield1 = '20070205',
@.datefield2 = '09:35';
SELECT
@.datefield1,
@.datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
"Me" <Me@.discussions.microsoft.com> wrote in message
news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
> Hello!
> I have two dates each with the datetime format.
> However in one, only the date portion is being stored and in the other
> the time with the date of 1/1/1900 is being stored.
> For eg. datefield1 = '2/5/2007 00:00:00'
> datefield2 = '1/1/1900 09:35:00'
> How can I get only the date part in 'Datefield1' and the time part in
> 'Datefield2'
> the resultant should be a date format as I want to compare it with another
> datetime field.
> Thank you very much for your help!
> -Me
|||Hi Aaron,
Thanks for the reply!
However, this doesn't address my issue.
'cause I already have 'datefiedl1' and 'datefield2' in datetime format in a
table.
Therefore, if I issue a select on these fields even as smalldatetime, I get
the date in date+time format, all I want is to segregate the datetime into
date and time.
Please let me know if you have other ideas.
-Me
"Aaron Bertrand [SQL Server MVP]" wrote:

> Suggestion #1 is to store these values together. What are you gaining by
> splitting them apart?
> Suggestion #2 I'll demonstrate with local variables:
> DECLARE
> @.datefield1 SMALLDATETIME,
> @.datefield2 SMALLDATETIME;
> SELECT
> @.datefield1 = '20070205',
> @.datefield2 = '09:35';
> SELECT
> @.datefield1,
> @.datefield2,
> Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
>
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
>
>
|||Sorry, I thought you would be able to adapt the example to your own table.
Let's try from start to finish? The eventual query only changes by dropping
the @. signs and adding a FROM.
USE Tempdb;
GO
CREATE TABLE dbo.foo
(
datefield1 DATETIME,
datefield2 DATETIME
);
GO
SET NOCOUNT ON;
INSERT
dbo.foo
SELECT
'20070205 00:00',
'19000101 09:35';
SELECT
datefield1,
datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,datefield1), datefield2)
FROM
dbo.foo;
GO
DROP TABLE dbo.foo;
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F850AD2F-A629-4A41-9C5B-69DFE1BB9D0C@.microsoft.com...[vbcol=seagreen]
> Hi Aaron,
> Thanks for the reply!
> However, this doesn't address my issue.
> 'cause I already have 'datefiedl1' and 'datefield2' in datetime format in
> a
> table.
> Therefore, if I issue a select on these fields even as smalldatetime, I
> get
> the date in date+time format, all I want is to segregate the datetime into
> date and time.
> Please let me know if you have other ideas.
> -Me
>
> "Aaron Bertrand [SQL Server MVP]" wrote:

Date format retrieval

Hello!
I have two dates each with the datetime format.
However in one, only the date portion is being stored and in the other
the time with the date of 1/1/1900 is being stored.
For eg. datefield1 = '2/5/2007 00:00:00'
datefield2 = '1/1/1900 09:35:00'
How can I get only the date part in 'Datefield1' and the time part in
'Datefield2'
the resultant should be a date format as I want to compare it with another
datetime field.
Thank you very much for your help!
-MeSuggestion #1 is to store these values together. What are you gaining by
splitting them apart?
Suggestion #2 I'll demonstrate with local variables:
DECLARE
@.datefield1 SMALLDATETIME,
@.datefield2 SMALLDATETIME;
SELECT
@.datefield1 = '20070205',
@.datefield2 = '09:35';
SELECT
@.datefield1,
@.datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
"Me" <Me@.discussions.microsoft.com> wrote in message
news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
> Hello!
> I have two dates each with the datetime format.
> However in one, only the date portion is being stored and in the other
> the time with the date of 1/1/1900 is being stored.
> For eg. datefield1 = '2/5/2007 00:00:00'
> datefield2 = '1/1/1900 09:35:00'
> How can I get only the date part in 'Datefield1' and the time part in
> 'Datefield2'
> the resultant should be a date format as I want to compare it with another
> datetime field.
> Thank you very much for your help!
> -Me|||Hi Aaron,
Thanks for the reply!
However, this doesn't address my issue.
'cause I already have 'datefiedl1' and 'datefield2' in datetime format in a
table.
Therefore, if I issue a select on these fields even as smalldatetime, I get
the date in date+time format, all I want is to segregate the datetime into
date and time.
Please let me know if you have other ideas.
-Me
"Aaron Bertrand [SQL Server MVP]" wrote:
> Suggestion #1 is to store these values together. What are you gaining by
> splitting them apart?
> Suggestion #2 I'll demonstrate with local variables:
> DECLARE
> @.datefield1 SMALLDATETIME,
> @.datefield2 SMALLDATETIME;
> SELECT
> @.datefield1 = '20070205',
> @.datefield2 = '09:35';
> SELECT
> @.datefield1,
> @.datefield2,
> Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
>
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
> > Hello!
> >
> > I have two dates each with the datetime format.
> > However in one, only the date portion is being stored and in the other
> > the time with the date of 1/1/1900 is being stored.
> >
> > For eg. datefield1 = '2/5/2007 00:00:00'
> > datefield2 = '1/1/1900 09:35:00'
> >
> > How can I get only the date part in 'Datefield1' and the time part in
> > 'Datefield2'
> >
> > the resultant should be a date format as I want to compare it with another
> > datetime field.
> >
> > Thank you very much for your help!
> > -Me
>
>|||Sorry, I thought you would be able to adapt the example to your own table.
Let's try from start to finish? The eventual query only changes by dropping
the @. signs and adding a FROM.
USE Tempdb;
GO
CREATE TABLE dbo.foo
(
datefield1 DATETIME,
datefield2 DATETIME
);
GO
SET NOCOUNT ON;
INSERT
dbo.foo
SELECT
'20070205 00:00',
'19000101 09:35';
SELECT
datefield1,
datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,datefield1), datefield2)
FROM
dbo.foo;
GO
DROP TABLE dbo.foo;
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F850AD2F-A629-4A41-9C5B-69DFE1BB9D0C@.microsoft.com...
> Hi Aaron,
> Thanks for the reply!
> However, this doesn't address my issue.
> 'cause I already have 'datefiedl1' and 'datefield2' in datetime format in
> a
> table.
> Therefore, if I issue a select on these fields even as smalldatetime, I
> get
> the date in date+time format, all I want is to segregate the datetime into
> date and time.
> Please let me know if you have other ideas.
> -Me
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Suggestion #1 is to store these values together. What are you gaining by
>> splitting them apart?
>> Suggestion #2 I'll demonstrate with local variables:
>> DECLARE
>> @.datefield1 SMALLDATETIME,
>> @.datefield2 SMALLDATETIME;
>> SELECT
>> @.datefield1 = '20070205',
>> @.datefield2 = '09:35';
>> SELECT
>> @.datefield1,
>> @.datefield2,
>> Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
>>
>> "Me" <Me@.discussions.microsoft.com> wrote in message
>> news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
>> > Hello!
>> >
>> > I have two dates each with the datetime format.
>> > However in one, only the date portion is being stored and in the other
>> > the time with the date of 1/1/1900 is being stored.
>> >
>> > For eg. datefield1 = '2/5/2007 00:00:00'
>> > datefield2 = '1/1/1900 09:35:00'
>> >
>> > How can I get only the date part in 'Datefield1' and the time part in
>> > 'Datefield2'
>> >
>> > the resultant should be a date format as I want to compare it with
>> > another
>> > datetime field.
>> >
>> > Thank you very much for your help!
>> > -Me
>>

Date format retrieval

Hello!
I have two dates each with the datetime format.
However in one, only the date portion is being stored and in the other
the time with the date of 1/1/1900 is being stored.
For eg. datefield1 = '2/5/2007 00:00:00'
datefield2 = '1/1/1900 09:35:00'
How can I get only the date part in 'Datefield1' and the time part in
'Datefield2'
the resultant should be a date format as I want to compare it with another
datetime field.
Thank you very much for your help!
-MeSuggestion #1 is to store these values together. What are you gaining by
splitting them apart?
Suggestion #2 I'll demonstrate with local variables:
DECLARE
@.datefield1 SMALLDATETIME,
@.datefield2 SMALLDATETIME;
SELECT
@.datefield1 = '20070205',
@.datefield2 = '09:35';
SELECT
@.datefield1,
@.datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
"Me" <Me@.discussions.microsoft.com> wrote in message
news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
> Hello!
> I have two dates each with the datetime format.
> However in one, only the date portion is being stored and in the other
> the time with the date of 1/1/1900 is being stored.
> For eg. datefield1 = '2/5/2007 00:00:00'
> datefield2 = '1/1/1900 09:35:00'
> How can I get only the date part in 'Datefield1' and the time part in
> 'Datefield2'
> the resultant should be a date format as I want to compare it with another
> datetime field.
> Thank you very much for your help!
> -Me|||Hi Aaron,
Thanks for the reply!
However, this doesn't address my issue.
'cause I already have 'datefiedl1' and 'datefield2' in datetime format in a
table.
Therefore, if I issue a select on these fields even as smalldatetime, I get
the date in date+time format, all I want is to segregate the datetime into
date and time.
Please let me know if you have other ideas.
-Me
"Aaron Bertrand [SQL Server MVP]" wrote:

> Suggestion #1 is to store these values together. What are you gaining by
> splitting them apart?
> Suggestion #2 I'll demonstrate with local variables:
> DECLARE
> @.datefield1 SMALLDATETIME,
> @.datefield2 SMALLDATETIME;
> SELECT
> @.datefield1 = '20070205',
> @.datefield2 = '09:35';
> SELECT
> @.datefield1,
> @.datefield2,
> Together = DATEADD(DAY, DATEDIFF(DAY,0,@.datefield1), @.datefield2);
>
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:07F8D7FD-369A-4CA3-AA9F-7CEE2F678815@.microsoft.com...
>
>|||Sorry, I thought you would be able to adapt the example to your own table.
Let's try from start to finish? The eventual query only changes by dropping
the @. signs and adding a FROM.
USE Tempdb;
GO
CREATE TABLE dbo.foo
(
datefield1 DATETIME,
datefield2 DATETIME
);
GO
SET NOCOUNT ON;
INSERT
dbo.foo
SELECT
'20070205 00:00',
'19000101 09:35';
SELECT
datefield1,
datefield2,
Together = DATEADD(DAY, DATEDIFF(DAY,0,datefield1), datefield2)
FROM
dbo.foo;
GO
DROP TABLE dbo.foo;
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F850AD2F-A629-4A41-9C5B-69DFE1BB9D0C@.microsoft.com...[vbcol=seagreen]
> Hi Aaron,
> Thanks for the reply!
> However, this doesn't address my issue.
> 'cause I already have 'datefiedl1' and 'datefield2' in datetime format in
> a
> table.
> Therefore, if I issue a select on these fields even as smalldatetime, I
> get
> the date in date+time format, all I want is to segregate the datetime into
> date and time.
> Please let me know if you have other ideas.
> -Me
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

date format in raiserror description

I need to return a date to the app if a stored procedure fails (when I raise
an error in it). I have just included the date in the error string, but
wanting to internationalise the app, need to be able to display the date is
the local format. What is the best way to return this information to the
app/user?Return it as an unambiguous date format (e.g. YYYYMMDD). Let the app
localize it (e.g. VBScript / ASP will apply locale to formatdatetime()).
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:R5CdnUa6P8Y7vcnfRVn-uA@.mycybernet.net...
> I need to return a date to the app if a stored procedure fails (when I
raise
> an error in it). I have just included the date in the error string, but
> wanting to internationalise the app, need to be able to display the date
is
> the local format. What is the best way to return this information to the
> app/user?
>|||I do not want to add any output parameters. Is there a way of returning an
error description and the date when raising the error?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O5ORqGtOFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Return it as an unambiguous date format (e.g. YYYYMMDD). Let the app
> localize it (e.g. VBScript / ASP will apply locale to formatdatetime()).
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
> news:R5CdnUa6P8Y7vcnfRVn-uA@.mycybernet.net...
> raise
> is
>|||On Wed, 6 Apr 2005 14:33:49 -0400, David J Rose wrote:

>I do not want to add any output parameters. Is there a way of returning an
>error description and the date when raising the error?
Hi David,
As far as I know: no.
BTW, I don't think Aaron intended to suggest you use an output
parameter. I think that he wanted to suggest that you include the date
in YYYYMMDD format in the error message, let your client intrercept the
error message, find the YYYYMMDD value and replace it with a formatted
date.
(And if Aaron didn't mean to suggest that, then I will <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> (And if Aaron didn't mean to suggest that, then I will <g> )
No, that's exactly what I meant. Currently I presume he is doing:
SET @.msg = 'this is an error on '+CONVERT(VARCHAR, GETDATE())+'...'
And I was suggesting an unambiguous format, such as:
SET @.msg = 'this is an error on '+CONVERT(CHAR(8), GETDATE(), 112)+'...'
Let the client interpret it however it wants to, or just leave it in the
unambiguous format. Whether you're in the UK or the US or anywhere else,
YYYYMMDD or YYYY-MM-DD is a heck of a lot more clear than 06/05/04 ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Date Format

I have a written a stored procedure and excecuting it and displaying it in gridview(ASP.NET). I am getting Birthdate in the form of8/23/1956 12:00:00 AM. I just need8/23/1956. I have defined it as smalldatetime in stored procedure. How can I get it in that format ?

HELP.......

In your .net code, if the object is of type DateTime you can call ToShortDate.

|||

Either you can convert the datetime to a varchar in your stored procedure:

select convert(varchar(10), getdate(), 103)

... or you can format the output in the GridView:

<asp:BoundFieldDataField="now"DataFormatString="{0:dd/MM/yyyy}"HtmlEncode="false"/>

|||

Set the DataFormatString, either in designer or HTML:

<asp:BoundField DataField="Date" DataFormatString="{0:dd-MMM-yy}" HeaderText="Date" HtmlEncode="false" />

|||

You can read more about CONVERT here:http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

...and more about DataFormatString here:http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx

|||

Thanks a bunch ... millepag

That CONVERT thing worked for me!!!!

Date Format

Hi,
I have a datetime field in my table and whose values are stored as "2004-07-29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JP
hi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP