I have a parameter called @.To that utilizes the date field in my datasource,
What I need it to do is to give the data that ends at @.To and that starts at
the first day of the month specified in the parameter @.To.
So I have constructed this query
WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
However this doesn't work :(
So I do it this way:
WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
This does work, however the parameter must be entered in the following
format Apr 16 2004 (which is useless to me)
Can anyone out there help?
AshYou might want to use an expression-based command text, where you write a
VB.NET expression which evaluates into a SQL query commandtext. However, you
have to be careful to not mix up SQL and VB.NET syntax. You might want to
checkout this walkthrough in BOL:
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
Using an expression-based command text you could then use VB functions like
CDate(), etc. to dynamically construct the dates you need in the query.
There are many options for your case. Assuming you have have two DateTime
report parameters:
="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
smalldatatime))"
You could then initialize these two report parameters with an
expression-based default value that automatically calculates the first and
the last day of the current month:
StartDate:
=DateValue(Month(Today) & " 1," & Year(Today))
EndDate:
=DateValue(Month(Today) & " 1," &
Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
Month(Today))-1)
Information about DateTime / conversion functions is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> I have a parameter called @.To that utilizes the date field in my
datasource,
> What I need it to do is to give the data that ends at @.To and that starts
at
> the first day of the month specified in the parameter @.To.
> So I have constructed this query
> WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> However this doesn't work :(
> So I do it this way:
> WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> This does work, however the parameter must be entered in the following
> format Apr 16 2004 (which is useless to me)
> Can anyone out there help?
> Ash
>|||Thanks for the quick reply!!!
I do not want to use two parameters only one. And sadly I just don't have
the knowledge of VB to construct these expressions. I am basically there
using SQL only code and will continue the battle.
Thanks for your help.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O6lS%23b1VEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You might want to use an expression-based command text, where you write a
> VB.NET expression which evaluates into a SQL query commandtext. However,
you
> have to be careful to not mix up SQL and VB.NET syntax. You might want to
> checkout this walkthrough in BOL:
>
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
> Using an expression-based command text you could then use VB functions
like
> CDate(), etc. to dynamically construct the dates you need in the query.
> There are many options for your case. Assuming you have have two DateTime
> report parameters:
> ="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
> smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
> smalldatatime))"
> You could then initialize these two report parameters with an
> expression-based default value that automatically calculates the first and
> the last day of the current month:
> StartDate:
> =DateValue(Month(Today) & " 1," & Year(Today))
> EndDate:
> =DateValue(Month(Today) & " 1," &
> Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
> Month(Today))-1)
>
> Information about DateTime / conversion functions is available on MSDN:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "AshVsAOD" <.> wrote in message
> news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> > I have a parameter called @.To that utilizes the date field in my
> datasource,
> > What I need it to do is to give the data that ends at @.To and that
starts
> at
> > the first day of the month specified in the parameter @.To.
> >
> > So I have constructed this query
> >
> > WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> >
> > However this doesn't work :(
> >
> > So I do it this way:
> > WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> >
> > This does work, however the parameter must be entered in the following
> > format Apr 16 2004 (which is useless to me)
> >
> > Can anyone out there help?
> >
> > Ash
> >
> >
>
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Thursday, March 29, 2012
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?
>
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 Range Problem
I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql
Tuesday, March 27, 2012
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
>
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 Query question
I am trying to access one of our SQL student tables by the change_date field. What is the proper way to get results when looking for a change_date >= "9/25/2006".
The field data looks like this: 9/25/2006 8:15:02 AM
Thx
The answer depends on how you are accessing the database. What application and/or API are you using? And what difficulties are you encountering?|||I'm using the the query option in SQL Server Enterprise Manager. I'm getting no results when I know the data is there.
Thx
|||Your change_date is most likely a Date/Time data type. In which case your sql string will look like this:
change_date >= #9/25/2006#
Whenever your querying data from a table, you need to think about what type of data of the fields you want to filter you search on.
If it's a date, time, or both, your gonna wrap your data within numeric/lbs. signs (Shift-3).
ex: date >= #1/1/2000#
If it's a string, your gonna wrap your data in single quotes.
ex: string_value = 'my value'
If it's a integer, you will not need to wrap the data in anything.
ex: integer_value = 100
|||In order to retreive data from a table, where the data contains a time component other than midnight, you will have to accomodate the time issue in your search criteria.
One of the best methods is the following:
WHERE ( Change_Date >= '2006/09/25'
AND Change_Date < '2006/09/26'
This 'brackets' the day from midnight the 25th until just before midnight the 26th. Therefore the time of day is immaterial -all times during the day will be retreived. And it will use any indexing that may be available for Change_Date.
|||AJ,
You forgot to remove your Access Developer brain and insert the SQL Server brain this morning. (Long holidays do that to us. 
T-SQL requires single quotes around the datetime values, whereas Access uses the pound sign (#).
|||Arnie,
Works great, thanks.
Date Query Problem
Hi All,
I have a table that stores IDs and dates:
CREATE TABLE table_X
(
StationID nvarchar(10) NOT NULL,
FullDate datetime NOT NULL
)
In the fulldate field there are dates one for each day and station.
sometimes a station does not report the date to this table. Sometimes it
doesn't do it for a series of days. I want to write a query that can give
me all of the days that are missing or at the very least the start of the
missing period and the end of the missing period. I tcan think of how to do
this to save my life!!!!!
Any help is greatly appreciated
Calvin XUse a left join against a calendar table.
http://www.aspfaq.com/2519
"Calvin X" <spam freerobotno_spammingdrone@.rotsnail.com> wrote in message
news:u2WJwXBUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table that stores IDs and dates:
> CREATE TABLE table_X
> (
> StationID nvarchar(10) NOT NULL,
> FullDate datetime NOT NULL
> )
>
> In the fulldate field there are dates one for each day and station.
> sometimes a station does not report the date to this table. Sometimes it
> doesn't do it for a series of days. I want to write a query that can give
> me all of the days that are missing or at the very least the start of the
> missing period and the end of the missing period. I tcan think of how to
> do this to save my life!!!!!
>
> Any help is greatly appreciated
> Calvin X
>|||CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
IsoDate varchar(10),
isW
day BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
Now, let's populate it with data. Let's assume we want 30 years of data,
from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20000101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
GO
CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
GO
INSERT Calendar(dt)
SELECT DATEADD(DAY, Number, '20000101')
FROM dbo.Numbers
WHERE Number <= 10957
ORDER BY Number
UPDATE dbo.Calendar SET
isWday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
IsoDate = CONVERT(varchar(10),IsoDate,112),
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt)
GO
SELECT * from TableX
WHERE FULLDATE NOT IN
(SELECT ISodate from Calender)
--If you want all the values , also the NUL values -->
SELECT * from TableX X
LEFT JOIN
Calender C
ON x.Fulldate = C.IsoDate
--best thing is t convert the date to isodate
CONERT(varchar(10),Fulldate,112)
If you still got problem raise a hand or send me an email, i will postit
back to the ng if solved.
HTH, Jens Suessmeyer.
"Calvin X" <spam freerobotno_spammingdrone@.rotsnail.com> schrieb im
Newsbeitrag news:u2WJwXBUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table that stores IDs and dates:
> CREATE TABLE table_X
> (
> StationID nvarchar(10) NOT NULL,
> FullDate datetime NOT NULL
> )
>
> In the fulldate field there are dates one for each day and station.
> sometimes a station does not report the date to this table. Sometimes it
> doesn't do it for a series of days. I want to write a query that can give
> me all of the days that are missing or at the very least the start of the
> missing period and the end of the missing period. I tcan think of how to
> do this to save my life!!!!!
>
> Any help is greatly appreciated
> Calvin X
>|||Wow, that's some pretty good cut & paste action, however there are some
inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
Number <= 10957, and you insert the same dates twice by using "the old
fashioned way" AND using the Numbers table).
Not that my articles are infallible, but why not just point the user at the
source, which is far less prone to transcription errors. Never mind the
whole giving credit where credit is due part...
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE dbo.Calendar
> (
> dt SMALLDATETIME NOT NULL
> PRIMARY KEY CLUSTERED,
> IsoDate varchar(10),
> isWday BIT,
> isHoliday BIT,
> Y SMALLINT,
> FY SMALLINT,
> Q TINYINT,
> M TINYINT,
> D TINYINT,
> DW TINYINT,
> monthname VARCHAR(9),
> dayname VARCHAR(9),
> W TINYINT
> )
> GO
> Now, let's populate it with data. Let's assume we want 30 years of data,
> from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:|||I always do quote the sources where the solution is from, i think evervyone
know the great gainers in here with Aaron and Tibor (just to names these
two)
Whatever, programming always has to do with C&P and just setting the right
variables / names and values.
e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957,
Thank you for correcting me.
Not that my articles are infallible, but why not just point the user at the
> source, which is far less prone to transcription errors. Never mind the
> whole giving credit where credit is due part...
Just as i said above. I dont know the skill level of the original poster,
so trying to help him with giving him the right direction and code samples
wont hurt somebody ego as far as we are doing this all in here just-4-help.
Jens Suessmeyer.
"AB - MVP" <ten.xoc@.dnartreb.noraa> schrieb im Newsbeitrag
news:evM73kBUFHA.952@.TK2MSFTNGP10.phx.gbl...
> Wow, that's some pretty good cut & paste action, however there are some
> inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957, and you insert the same dates twice by using "the old
> fashioned way" AND using the Numbers table).
> Not that my articles are infallible, but why not just point the user at
> the source, which is far less prone to transcription errors. Never mind
> the whole giving credit where credit is due part...
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||I thought you were a new member and now I realize what AB means. Good you ar
e
back.
AMB
"AB - MVP" wrote:
> Wow, that's some pretty good cut & paste action, however there are some
> inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957, and you insert the same dates twice by using "the old
> fashioned way" AND using the Numbers table).
> Not that my articles are infallible, but why not just point the user at th
e
> source, which is far less prone to transcription errors. Never mind the
> whole giving credit where credit is due part...
>
>
>
> "Jens Sü?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
>
>|||> Just as i said above. I dont know the skill level of the original poster,
> so trying to help him with giving him the right direction and code samples
> wont hurt somebody ego as far as we are doing this all in here
> just-4-help.
I understand, my main point was not that my feelings were hurt because my
article was chopped up and not credited (that happens all the time), it was
that the sloppiness of it will likely yield even further confusion / errors.|||>I thought you were a new member and now I realize what AB means. Good you
>are
> back.
Thanks.
It doesn't take me forever to see past thoughtless statements by an
ignoramus (or multiple). Not that I have any more respect than I did a w
ago, I just
off and then increase the size of my twitfile.
A|||I dont wanna argue with you on that,
perhaps we meet sometime and drink this out. ;-)
Just to say, Hats off for the work youve done.. :-)
Jens
"AB - MVP" <ten.xoc@.dnartreb.noraa> schrieb im Newsbeitrag
news:OyqUOtBUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> I understand, my main point was not that my feelings were hurt because my
> article was chopped up and not credited (that happens all the time), it
> was that the sloppiness of it will likely yield even further confusion /
> errors.
>|||You can get the start of the gaps with:
SELECT A.* FROM table A LEFT JOIN table B ON A.dateField=B.dateField-1
WHERE B.primaryKey IS NULL
or the end of the gaps with:
SELECT A.* FROM table A LEFT JOIN table B ON A.dateField=B.dateField+1
WHERE B.primaryKey IS NULL
It does have the side effect of including the first record and won't be as
efficient as using a calendar.sql
I have a table that stores IDs and dates:
CREATE TABLE table_X
(
StationID nvarchar(10) NOT NULL,
FullDate datetime NOT NULL
)
In the fulldate field there are dates one for each day and station.
sometimes a station does not report the date to this table. Sometimes it
doesn't do it for a series of days. I want to write a query that can give
me all of the days that are missing or at the very least the start of the
missing period and the end of the missing period. I tcan think of how to do
this to save my life!!!!!
Any help is greatly appreciated
Calvin XUse a left join against a calendar table.
http://www.aspfaq.com/2519
"Calvin X" <spam freerobotno_spammingdrone@.rotsnail.com> wrote in message
news:u2WJwXBUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table that stores IDs and dates:
> CREATE TABLE table_X
> (
> StationID nvarchar(10) NOT NULL,
> FullDate datetime NOT NULL
> )
>
> In the fulldate field there are dates one for each day and station.
> sometimes a station does not report the date to this table. Sometimes it
> doesn't do it for a series of days. I want to write a query that can give
> me all of the days that are missing or at the very least the start of the
> missing period and the end of the missing period. I tcan think of how to
> do this to save my life!!!!!
>
> Any help is greatly appreciated
> Calvin X
>|||CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
IsoDate varchar(10),
isW
day BIT,isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO
Now, let's populate it with data. Let's assume we want 30 years of data,
from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:
SET NOCOUNT ON
DECLARE @.dt SMALLDATETIME
SET @.dt = '20000101'
WHILE @.dt < '20300101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END
GO
CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1024
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
GO
INSERT Calendar(dt)
SELECT DATEADD(DAY, Number, '20000101')
FROM dbo.Numbers
WHERE Number <= 10957
ORDER BY Number
UPDATE dbo.Calendar SET
isW
day = CASEWHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
IsoDate = CONVERT(varchar(10),IsoDate,112),
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt)
GO
SELECT * from TableX
WHERE FULLDATE NOT IN
(SELECT ISodate from Calender)
--If you want all the values , also the NUL values -->
SELECT * from TableX X
LEFT JOIN
Calender C
ON x.Fulldate = C.IsoDate
--best thing is t convert the date to isodate
CONERT(varchar(10),Fulldate,112)
If you still got problem raise a hand or send me an email, i will postit
back to the ng if solved.
HTH, Jens Suessmeyer.
"Calvin X" <spam freerobotno_spammingdrone@.rotsnail.com> schrieb im
Newsbeitrag news:u2WJwXBUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table that stores IDs and dates:
> CREATE TABLE table_X
> (
> StationID nvarchar(10) NOT NULL,
> FullDate datetime NOT NULL
> )
>
> In the fulldate field there are dates one for each day and station.
> sometimes a station does not report the date to this table. Sometimes it
> doesn't do it for a series of days. I want to write a query that can give
> me all of the days that are missing or at the very least the start of the
> missing period and the end of the missing period. I tcan think of how to
> do this to save my life!!!!!
>
> Any help is greatly appreciated
> Calvin X
>|||Wow, that's some pretty good cut & paste action, however there are some
inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
Number <= 10957, and you insert the same dates twice by using "the old
fashioned way" AND using the Numbers table).
Not that my articles are infallible, but why not just point the user at the
source, which is far less prone to transcription errors. Never mind the
whole giving credit where credit is due part...
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE dbo.Calendar
> (
> dt SMALLDATETIME NOT NULL
> PRIMARY KEY CLUSTERED,
> IsoDate varchar(10),
> isW
day BIT,> isHoliday BIT,
> Y SMALLINT,
> FY SMALLINT,
> Q TINYINT,
> M TINYINT,
> D TINYINT,
> DW TINYINT,
> monthname VARCHAR(9),
> dayname VARCHAR(9),
> W TINYINT
> )
> GO
> Now, let's populate it with data. Let's assume we want 30 years of data,
> from 2000-01-01 through 2029-12-31. You can do it the old fashioned way:|||I always do quote the sources where the solution is from, i think evervyone
know the great gainers in here with Aaron and Tibor (just to names these
two)
Whatever, programming always has to do with C&P and just setting the right
variables / names and values.
e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957,
Thank you for correcting me.
Not that my articles are infallible, but why not just point the user at the
> source, which is far less prone to transcription errors. Never mind the
> whole giving credit where credit is due part...
Just as i said above. I dont know the skill level of the original poster,
so trying to help him with giving him the right direction and code samples
wont hurt somebody ego as far as we are doing this all in here just-4-help.
Jens Suessmeyer.
"AB - MVP" <ten.xoc@.dnartreb.noraa> schrieb im Newsbeitrag
news:evM73kBUFHA.952@.TK2MSFTNGP10.phx.gbl...
> Wow, that's some pretty good cut & paste action, however there are some
> inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957, and you insert the same dates twice by using "the old
> fashioned way" AND using the Numbers table).
> Not that my articles are infallible, but why not just point the user at
> the source, which is far less prone to transcription errors. Never mind
> the whole giving credit where credit is due part...
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
>|||I thought you were a new member and now I realize what AB means. Good you ar
e
back.
AMB
"AB - MVP" wrote:
> Wow, that's some pretty good cut & paste action, however there are some
> inconsistencies (e.g. you use 1024 for the SCOPE_IDENTITY check but WHERE
> Number <= 10957, and you insert the same dates twice by using "the old
> fashioned way" AND using the Numbers table).
> Not that my articles are infallible, but why not just point the user at th
e
> source, which is far less prone to transcription errors. Never mind the
> whole giving credit where credit is due part...
>
>
>
> "Jens Sü?meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:%23gVhKhBUFHA.584@.TK2MSFTNGP15.phx.gbl...
>
>|||> Just as i said above. I dont know the skill level of the original poster,
> so trying to help him with giving him the right direction and code samples
> wont hurt somebody ego as far as we are doing this all in here
> just-4-help.
I understand, my main point was not that my feelings were hurt because my
article was chopped up and not credited (that happens all the time), it was
that the sloppiness of it will likely yield even further confusion / errors.|||>I thought you were a new member and now I realize what AB means. Good you
>are
> back.
Thanks.
It doesn't take me forever to see past thoughtless statements by an
ignoramus (or multiple). Not that I have any more respect than I did a w
ago, I just
off and then increase the size of my twitfile.A|||I dont wanna argue with you on that,
perhaps we meet sometime and drink this out. ;-)
Just to say, Hats off for the work youve done.. :-)
Jens
"AB - MVP" <ten.xoc@.dnartreb.noraa> schrieb im Newsbeitrag
news:OyqUOtBUFHA.3188@.TK2MSFTNGP09.phx.gbl...
> I understand, my main point was not that my feelings were hurt because my
> article was chopped up and not credited (that happens all the time), it
> was that the sloppiness of it will likely yield even further confusion /
> errors.
>|||You can get the start of the gaps with:
SELECT A.* FROM table A LEFT JOIN table B ON A.dateField=B.dateField-1
WHERE B.primaryKey IS NULL
or the end of the gaps with:
SELECT A.* FROM table A LEFT JOIN table B ON A.dateField=B.dateField+1
WHERE B.primaryKey IS NULL
It does have the side effect of including the first record and won't be as
efficient as using a calendar.sql
Date Query
Hi all:
I am trying to get data that wasn't used in the last six month. Created_on (date field)
select Category_tree_value, Created_on from workitem
where Created_on (field was used from the last six months)
Created_on format (2005-10-01 00:00:00.000)
Thanking everyone in advance.
LystraTry
where Created_on < DateAdd(Month, -6, GetDate())
I am trying to get data that wasn't used in the last six month. Created_on (date field)
select Category_tree_value, Created_on from workitem
where Created_on (field was used from the last six months)
Created_on format (2005-10-01 00:00:00.000)
Thanking everyone in advance.
LystraTry
where Created_on < DateAdd(Month, -6, GetDate())
Date problems between VB and SQL
I have a problem that I have spent an age on and can't resolve.
Basically, I have a table that has a date field. I have created SP's to
return values within a given date range.
The problem that I have is that when I append some data to the table,
inconsistency occurs.
What basically happens is that when I run the SP within SQL Analyser,
the resultset returns correctly, including ALL data between the two date
ranges. I can see the query in SQL Profiler and can see that the correct
SP and parameters are being passed to SQL.
The problem occurs when I run the SP from VB. If I pass the date
parameters from the original table (before I append any data), the
correct set of results are returned. However, if I then APPEND some
data, VB returns a "Runtime Error 3265 - this item cannot be found in
the collection corresponding to the requested name or ordinal."
In other words, if the original table contains data from say the 1st to
the 8th February, the SP's work fine. If I then add data for the 9th, VB
returns the error indicated.
What is really strange is that at all times, the correct SP and
parameters are being passed to SQL (I can see this using SQL Profiler)
and if I cut and paste the SP and parameters into SQL Analyser, the
correct data is always returned, even if the date range includes the
appended data.
The table is set up correctly using the correct data type for the
column.
I am thinking that this has something to do with the appended data - it
all appends OK so I assume that SQL is happy with the data and data
types. Would it have something to do with the SP being created on the
original table and not on the latest table that has had data appended to
it?
I'll also post this in the VB section as I'm not really sure where this
item should be posted.
Very many thanks for your kind consideration.
Shane
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Shane (Shane) writes:
> The problem occurs when I run the SP from VB. If I pass the date
> parameters from the original table (before I append any data), the
> correct set of results are returned. However, if I then APPEND some
> data, VB returns a "Runtime Error 3265 - this item cannot be found in
> the collection corresponding to the requested name or ordinal."
> In other words, if the original table contains data from say the 1st to
> the 8th February, the SP's work fine. If I then add data for the 9th, VB
> returns the error indicated.
> What is really strange is that at all times, the correct SP and
> parameters are being passed to SQL (I can see this using SQL Profiler)
> and if I cut and paste the SP and parameters into SQL Analyser, the
> correct data is always returned, even if the date range includes the
> appended data.
A lot of text, but little code...
I don't even understand when you get the VB Error. Is when you use .Append?
Or is it when you call the procedure after you have appended?
And with which collection do you get the error?
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, March 25, 2012
Date portion comparison of a datetime field
I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:
> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:
> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.
date parsing
Hello,
I have a source with two smalldatetime fields, the first field contains 8/1/2006 12:00:00 AM, and the second field contains 8/10/2006 7:57:00 PM.
I would like to have the date from the first field and the time from the second field. No chance of changing the source system to do this for me.
What I have so far works, except the time portion is converted to 19:57:00 instead of 7:57:00 p.m. Any Ideas? My expression is below.
(DT_STR,2,1252)DATEPART("month",FIELD1) + "/" + (DT_STR,2,1252)DATEPART("Day",FIELD1) + "/" + (DT_STR,4,1252)DATEPART("Year",FIELD1) + " " + (DT_STR,2,1252)DATEPART("Hour",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("Minute",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("SS",FIELD2)
Thanks!
Try casting the values as DT_DBTIME or DT_DBDATE.
-Jamie
|||I added a data conversion transform to convert the output from the derived column transform to a database timestamp and it appears to be working. I probably could do all of this in one transformation, but this will work for now. Thanks!|||
You could wrap the cast to DT_DBTIMESTAMP around your whole expression in the derived column to avoid using the data convert downstream.
Mark
|||Thanks. I tried that and it worked great.Thursday, March 22, 2012
Date Parameter Problem! Please help!
I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2... and so on.
There is not a Period column to reference the fields to and I am not sure how to
use a date range and apply it to one parameter. Is there a way to use
the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I did get some advice on using an if statement to reference the parameter but I receive the error message that I must declar the scalar value @.Period.
Here is the simple query I used to just see if the query would run based on the parameter(be nice...I am a newbie to SQL and RS)
In my Data tab:
IF @.Period = 1 SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN '03/06/2006' AND '04/02/2006'
For the report parameter:
Label Value
Period 1 1
I have used the IIF expression and such but this is just a different situation and I am pulling my hair out trying to find an answer.
Any help is greatly appreciated!
I
I'm sure there are a few different ways to approach this, but this could be one.
1) Create a parameter called @.PeriodStart like you did. Make this a datetime parameter. The label for this parameter could be a number (1, 2, 3.. n) and the value would be a date. You could hard-code the periods in through the Available Values section and select non-queried. For label you could put 1 and for value '1/1/2006' and 2 then 1/7/2006... or whatever you wanted to define for your period start dates. (If you wanted to make this more dynamic, you could create a dataset that somehow used sql functions to get these dates... You could create a DateDimension table or a Period table..)
2) Create a dataset called something like PeriodEndDataSet. The query could be something like this: SELECT DATEADD(MONTH, 1, @.PeriodStart) AS PeriodEndDate. (you can make the 1 month be anything you wanted).
3) Create a second parameter called @.PeriodEnd. You would want this to be a hidden parameter. In the Parameter editor Select Queried from available values section. Select your dataset and label & value fields. Also set the default value to come from the same query.
4) In the query for your report (the main data set), you can create your query to do this:
SELECT [Date Started], Store
FROM trialtbl
WHERE [Date Started] BETWEEN @.PeriodStart AND @.PeriodEnd
That seems like a lot of work... I would almost recommend creating a lookup/dimension table to store this period information for you. You could then use this across many reports.
Regards,
Dan
Date Parameter and text box question
I have a report that runs transcripts for people. I have a date parameter setup along with last and first name. The date parameter field is used to specify what year (ex. 1/1/2006 - 12/31/2006) to pull the information for.
Now..I have a text box at the bottom (Footer) of my report that says something to the affect of "Official transcripts for 2006". There will be times when I have to run the report using a different year other then "2006" in the date parameter field. How do I have the text at the bottom change to reflect the year I'm reporting on? If I use dates say in 2004 I need the text at the bottom to reflect that year and not 2006. Can this be accomplished?
Thx,
Bill
Sounds like you need to parse the parameter value (Parameters!ParameterName.Value) to get to the year portion. You can add a code-behind VB.NET function to help with this as demonstrated at the beginning of this article.|||="Official transcripts for 2006" & Parameters!YourDateParameterName.Value
Try this. . .
Wednesday, March 21, 2012
Date Only Data Type
I would like to have a field in a table that is a Date only data type instea
d
of the datetime. Could someone offer any advice on this in SQL Server 2005.
ThanksDoesn't exists
You could store YYYYMMDD in a char(8) field instead
http://sqlservercode.blogspot.com/|||No such thing.
http://www.aspfaq.com/2206
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||>> I would like to have a field [sic] in a table that is a DATE only data type in
stead of the DATETIME. Could someone offer any advice on this in SQL Serve
r 2005. <<
You might want to learn the basics of RDBMS, so that you do not confuse
fields and columns.
Now, to answer your question: do it the right way! Time is not a point
(Chronons), but a duration expressed as half-open intervals. Thus a
day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the
ISO temporal model.
Read Rick Snodgrass at Univeristy of AZ for more details.|||If you want to treat the data as a date, without a time value, just ignore
the time value when your application updates or selects from the database.
The only time it makes a difference is if you are comparing two date values.
If you always insert/update your date values without specifying the time,
then the time is set to midnight of that day. As long as you are
consistent, this has the same effect as if you didn't store the time at all,
and you wont run into issues when comparing dates.
You could build in a trigger that insures the time value is set to 00:00:00
on inserts and updates as well, which would guarantee consistency even if
the application programming mistakenly sets the time to another value.
The only other real difference is storage, and I doubt it is significant
enough to even give consideration to with today's storage costs.
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
> I would like to have a field in a table that is a Date only data type
instead
> of the datetime. Could someone offer any advice on this in SQL Server
2005.
> Thanks
>|||> The only other real difference is storage, and I doubt it is significant
> enough to even give consideration to with today's storage costs.
Well, I would suggest that if you only care about the date, then using
SMALLDATETIME will minimize storage requirements and make indexes more
efficient, and I don't know of any downside...
A|||I would agree with others that a Date only type has only marginal value as
you could treat all datetimes as 00:00:00.000 time to get the same result.
However, a Date only type can be mentally easier to deal with as you know
time can not effect what your doing. I actually added a TDate and TTime
UDTs to a sql project I did. Can use it as is or update it as needed if you
don't like the api choices. Get the project at link below.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I would agree with others that a Date only type has only marginal value as
I don't think so. People have been screaming for this for many years,
and I would very disappointed if it is not in the next version of SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||With 2005, they don't have to wait, unless they want to. I agree it has
some value, but maybe I missed a whole bunch of use cases. What are some of
the more profound use cases for Date only you have come across? TIA Erland.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974953149317Yazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I don't think so. People have been screaming for this for many years,
> and I would very disappointed if it is not in the next version of SQL
> Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> With 2005, they don't have to wait, unless they want to.
Why? The Date and Time datatypes were dropped from the product very early
on. I think most of it was due to the volume with which we complained about
its implementation (and by we I do mean myself, Erland and others). I'm
sure that's not what you mean, so while, yes, you could create your own UDT,
have fun with that, and let us know when you have something marginally
useful! The most complex UDT I've seen to date that was actually useable
was POINT. There is so much involved with date validation and
interoperability that it is unlikely you would be able to develop something
that would seamlessly integrate with the rest of the product (most notably
implicit conversion to/from datetime and acceptance as inputs to functions
like DATEDIFF/DATEADD/YEAR/MONTH/DAY).
> What are some of the more profound use cases for Date only you have come
> across?
A calendar table. Hire/fire date. Birth date. I'm sure if I spent more
than two minutes and reviewed all of the projects I've been involved with in
the past 10 years, I could come up with dozens of others. None of these
need time in most cases, and life would be much simpler if we didn't have to
truncate/validate/correct data going in or coming out, or when comparing, or
when displaying, or when exporting to XML, or ...
d
of the datetime. Could someone offer any advice on this in SQL Server 2005.
ThanksDoesn't exists
You could store YYYYMMDD in a char(8) field instead
http://sqlservercode.blogspot.com/|||No such thing.
http://www.aspfaq.com/2206
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||>> I would like to have a field [sic] in a table that is a DATE only data type in
stead of the DATETIME. Could someone offer any advice on this in SQL Serve
r 2005. <<
You might want to learn the basics of RDBMS, so that you do not confuse
fields and columns.
Now, to answer your question: do it the right way! Time is not a point
(Chronons), but a duration expressed as half-open intervals. Thus a
day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the
ISO temporal model.
Read Rick Snodgrass at Univeristy of AZ for more details.|||If you want to treat the data as a date, without a time value, just ignore
the time value when your application updates or selects from the database.
The only time it makes a difference is if you are comparing two date values.
If you always insert/update your date values without specifying the time,
then the time is set to midnight of that day. As long as you are
consistent, this has the same effect as if you didn't store the time at all,
and you wont run into issues when comparing dates.
You could build in a trigger that insures the time value is set to 00:00:00
on inserts and updates as well, which would guarantee consistency even if
the application programming mistakenly sets the time to another value.
The only other real difference is storage, and I doubt it is significant
enough to even give consideration to with today's storage costs.
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
> I would like to have a field in a table that is a Date only data type
instead
> of the datetime. Could someone offer any advice on this in SQL Server
2005.
> Thanks
>|||> The only other real difference is storage, and I doubt it is significant
> enough to even give consideration to with today's storage costs.
Well, I would suggest that if you only care about the date, then using
SMALLDATETIME will minimize storage requirements and make indexes more
efficient, and I don't know of any downside...
A|||I would agree with others that a Date only type has only marginal value as
you could treat all datetimes as 00:00:00.000 time to get the same result.
However, a Date only type can be mentally easier to deal with as you know
time can not effect what your doing. I actually added a TDate and TTime
UDTs to a sql project I did. Can use it as is or update it as needed if you
don't like the api choices. Get the project at link below.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I would agree with others that a Date only type has only marginal value as
I don't think so. People have been screaming for this for many years,
and I would very disappointed if it is not in the next version of SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||With 2005, they don't have to wait, unless they want to. I agree it has
some value, but maybe I missed a whole bunch of use cases. What are some of
the more profound use cases for Date only you have come across? TIA Erland.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974953149317Yazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I don't think so. People have been screaming for this for many years,
> and I would very disappointed if it is not in the next version of SQL
> Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> With 2005, they don't have to wait, unless they want to.
Why? The Date and Time datatypes were dropped from the product very early
on. I think most of it was due to the volume with which we complained about
its implementation (and by we I do mean myself, Erland and others). I'm
sure that's not what you mean, so while, yes, you could create your own UDT,
have fun with that, and let us know when you have something marginally
useful! The most complex UDT I've seen to date that was actually useable
was POINT. There is so much involved with date validation and
interoperability that it is unlikely you would be able to develop something
that would seamlessly integrate with the rest of the product (most notably
implicit conversion to/from datetime and acceptance as inputs to functions
like DATEDIFF/DATEADD/YEAR/MONTH/DAY).
> What are some of the more profound use cases for Date only you have come
> across?
A calendar table. Hire/fire date. Birth date. I'm sure if I spent more
than two minutes and reviewed all of the projects I've been involved with in
the past 10 years, I could come up with dozens of others. None of these
need time in most cases, and life would be much simpler if we didn't have to
truncate/validate/correct data going in or coming out, or when comparing, or
when displaying, or when exporting to XML, or ...
date need back off four year
I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.You can use the expression DateAdd(year, -4, GetDate()) in order to find the date four years ago. Without knowing a lot more about your table structures, etc. I can't make a good guess at what code you'll need.
-PatP|||thanks pat, i am using DTS and schedule to import the table to another database every night. My table has fields: Name, Address, County, QualityDate. Quality is short date type. Is that good for you to figure out when i create job how to write a query in where clause, such as, select Name, Address, County, QualityDate from table1 where ...... (i don't know how to do it) .Thanks.|||This won't be absolutely perfect, but you could get really close using:SELECT Name, Address, County, QualityDate
FROM SourceServer.SourceDatabase.dbo.SourceTable
WHERE QualityDate
BETWEEN Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121)
AND Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121) + ' 23:59'That snippet will pick up the rows that occured anytime on the day that is four years ago today. This should work Ok for 90+ years, which will be well past the point that SMALLDATETIME can represent!
-PatP|||thanks pat, i got it. Have a nice day!
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.You can use the expression DateAdd(year, -4, GetDate()) in order to find the date four years ago. Without knowing a lot more about your table structures, etc. I can't make a good guess at what code you'll need.
-PatP|||thanks pat, i am using DTS and schedule to import the table to another database every night. My table has fields: Name, Address, County, QualityDate. Quality is short date type. Is that good for you to figure out when i create job how to write a query in where clause, such as, select Name, Address, County, QualityDate from table1 where ...... (i don't know how to do it) .Thanks.|||This won't be absolutely perfect, but you could get really close using:SELECT Name, Address, County, QualityDate
FROM SourceServer.SourceDatabase.dbo.SourceTable
WHERE QualityDate
BETWEEN Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121)
AND Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121) + ' 23:59'That snippet will pick up the rows that occured anytime on the day that is four years ago today. This should work Ok for 90+ years, which will be well past the point that SMALLDATETIME can represent!
-PatP|||thanks pat, i got it. Have a nice day!
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
>
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
>
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
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
Monday, March 19, 2012
Date Issue
Good Afternoon,
I have a late shipment report, I added a field where it give me information whether a shipment is late or. This is what I have so far.
=DateDiff("d", Fields!SHIP_DATE.Value ,Fields!PROMISE.Value)
It is doing what I want it to do, except one problem, I need it to display only the ones that are late, not all of them. I tried sorting it our with
=DateDiff("d", Fields!SHIP_DATE.Value ,Fields!PROMISE.Value) < 0
But it gives me an error or it wont accept the sorting.
I'm all ears....please help.
Abner
why dont you modify your SQL query itself instead of doing it from report
Select * from OrderTable where DATEDIFF(day, SHIP_DATE, PROMISE ) < 0
rmaiya, thanks for the reply but I got it. The way I had it first was right, but had to do the sort under group properties, visibility and add my new field (where the expression was added) and add <= 0 and it works like a charm.
abner
Date issue
I have got an issue with dateformats. When i retrieve a date field from the
sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
want to compare it to the current time. This doesn't work. When i print the
current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it returns
the current date as 02/10/2005 17:40:00.
Your help is much appreciated.
Regards Ron WallegieRon Wallegie (wallegie@.planet.nl) writes:
> I have got an issue with dateformats. When i retrieve a date field from
> the sql server it is noted as 02/10/2005 17:40:00. everything fine so
> far. I want to compare it to the current time. This doesn't work. When i
> print the current datetime i get " 4 Nov 2005 5:40PM". How can i set sql
> so it returns the current date as 02/10/2005 17:40:00.
Preferrably you should return datetime value as-is from SQL Server, that
is no convert. The date value is then passed to the client as a binary
value, and the client will format it according to regional settings.
Same thing applies when you pass dates to SQL Server - pass them as
datetime values in parameters, and conversion will be performed by the
client API, again according to regional settings.
If you for some reason must work with date literals in SQL Server, use
the the format YYYYMMDD. This format is safe from different interpretations.
Note that SQL Server does never look at regional settings in Windows,
and has its own settings. And due to legacy, the safe format in Windows,
YYYY-MM-DD is not safe in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you just want to get the presentation do display as you specifiied, you
can format it to "print" by converting it to a varchar. Look up CAST and
CONVERT in BoL.
SELECT CONVERT(varchar(10), GetDate(), 101) + ' ' + CONVERT(varchar(8),
GetDate(), 108)
HTH,
John Scragg
"Ron Wallegie" wrote:
> I have got an issue with dateformats. When i retrieve a date field from th
e
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print th
e
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it retur
ns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>
>
sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
want to compare it to the current time. This doesn't work. When i print the
current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it returns
the current date as 02/10/2005 17:40:00.
Your help is much appreciated.
Regards Ron WallegieRon Wallegie (wallegie@.planet.nl) writes:
> I have got an issue with dateformats. When i retrieve a date field from
> the sql server it is noted as 02/10/2005 17:40:00. everything fine so
> far. I want to compare it to the current time. This doesn't work. When i
> print the current datetime i get " 4 Nov 2005 5:40PM". How can i set sql
> so it returns the current date as 02/10/2005 17:40:00.
Preferrably you should return datetime value as-is from SQL Server, that
is no convert. The date value is then passed to the client as a binary
value, and the client will format it according to regional settings.
Same thing applies when you pass dates to SQL Server - pass them as
datetime values in parameters, and conversion will be performed by the
client API, again according to regional settings.
If you for some reason must work with date literals in SQL Server, use
the the format YYYYMMDD. This format is safe from different interpretations.
Note that SQL Server does never look at regional settings in Windows,
and has its own settings. And due to legacy, the safe format in Windows,
YYYY-MM-DD is not safe in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you just want to get the presentation do display as you specifiied, you
can format it to "print" by converting it to a varchar. Look up CAST and
CONVERT in BoL.
SELECT CONVERT(varchar(10), GetDate(), 101) + ' ' + CONVERT(varchar(8),
GetDate(), 108)
HTH,
John Scragg
"Ron Wallegie" wrote:
> I have got an issue with dateformats. When i retrieve a date field from th
e
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print th
e
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it retur
ns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>
>
Date issue
I have got an issue with dateformats. When i retrieve a date field from the
sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
want to compare it to the current time. This doesn't work. When i print the
current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it returns
the current date as 02/10/2005 17:40:00.
Your help is much appreciated.
Regards Ron WallegiePrint converts to a string. What do you get when you say SELECT GETDATE()?
Different, no?
Keep in mind that I am having problems understanding your date for the very
reason that you chose an ambiguous date format. I suggest using YYYYMMDD or
YYYY-MM-DDTHH:MM:SS. These are ISO standards and are truthfully the only
safe formats you can use to represent dates in SQL Server. Anything else
can be misinterpreted by the database depending on regional settings,
dateformat, language, etc.
What do you mean by "compare it to the current time"? Compare what? A
value in a table?
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
>I have got an issue with dateformats. When i retrieve a date field from the
>sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
>want to compare it to the current time. This doesn't work. When i print the
>current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
>returns the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||If you want to compare two dates to see if they are the same.
SELECT
(CASE WHEN DATEDIFF( ms , myDateField , GETDATE() ) = 0 THEN 'Same' ELSE
'Different' END) AS DateCompare
FROM
myTable
If you're using SMALLDATETIME you can use DATEDIFF( mi , ... ), because it
is only accurate to a minute.
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I have got an issue with dateformats. When i retrieve a date field from
the
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print
the
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
returns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||Try using the convert function below.
By using the Convert function. The syntax for using the convert function
is: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
select convert(varchar,DateColumn,100) --> Nov 4 2005 11:08AM
or
I recommend converting both the date from the table and getdate()
using the function below in military time.
select convert(varchar,DateColumn,120) --> 2005-11-04 11:10:00
Select *
from table
where convert(varchar,DateColumn,120)=convert(
varchar,getdate(),120)
KT
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I have got an issue with dateformats. When i retrieve a date field from
the
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print
the
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
returns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||Hey Ron,
There are many ways to build up date strings, please google for them as MSDN
is quite limited in what it suggests. There is also a DB or Server setting
that controls this but I'm not sure what it is right now. Here are some
examples to get you started.
SELECT SUBSTRING(CONVERT(CHAR(12),GETDATE(),1),
1,8) + ' ' +
CONVERT(CHAR(12),GETDATE(),8)
select convert(smalldatetime,convert(char(12),g
etdate(),13))
select convert(datetime,convert(char(30),getdat
e(),21))
select convert(datetime,getdate(),10)|||Thx for your reply,
Hopefully this will help.
When printing getdate() i get " 4 Nov 2005 5:40PM". When retrieving the
table value i get 02/10/2005 17:40:00.
I created a script which works fine op a other server. When i do a select
getdate() on that server i get 02/10/2005 17:40:00 date format. How can i
change the format on the other server?
Regards Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:exxqlFW4FHA.3292@.tk2msftngp13.phx.gbl...
> Print converts to a string. What do you get when you say SELECT
GETDATE()?
> Different, no?
> Keep in mind that I am having problems understanding your date for the
very
> reason that you chose an ambiguous date format. I suggest using YYYYMMDD
or
> YYYY-MM-DDTHH:MM:SS. These are ISO standards and are truthfully the only
> safe formats you can use to represent dates in SQL Server. Anything else
> can be misinterpreted by the database depending on regional settings,
> dateformat, language, etc.
> What do you mean by "compare it to the current time"? Compare what? A
> value in a table?
>
>
> "Ron Wallegie" <wallegie@.hotmail.com> wrote in message
> news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
the
the
>|||Just to clairify...
SET DATEFORMAT DMY -- i am assuming it's 2nd oct '05
DECLARE @.ClientDate SMALLDATETIME , @.TimeNow SMALLDATETIME
SELECT @.ClientDate = '02/10/2005 17:40:00' , @.TimeNow = GETDATE()
SELECT
@.ClientDate
, @.TimeNow
, (CASE
WHEN DATEDIFF( mi , @.ClientDate , @.TimeNow ) = 0 THEN 'Same'
ELSE 'Different'
END) AS DateCompare
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:436b8e34$0$140$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want to compare two dates to see if they are the same.
> SELECT
> (CASE WHEN DATEDIFF( ms , myDateField , GETDATE() ) = 0 THEN 'Same'
ELSE
> 'Different' END) AS DateCompare
> FROM
> myTable
> If you're using SMALLDATETIME you can use DATEDIFF( mi , ... ), because it
> is only accurate to a minute.
>
> "Ron Wallegie" <wallegie@.hotmail.com> wrote in message
> news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> the
> the
> returns
>|||Why do you think you need to format dates to compare them? What exactly are
you trying to do?
ML|||When starting a procedure iwant to pass all the records in a table before
the getdate() value. After the procedure stops it deletes all value before
the getdat value.
I know there is a db setting which controls the format you get when rinting
getdate(). I don't know how...
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0F8F8DE5-4774-491B-A86E-41381F563B9F@.microsoft.com...
> Why do you think you need to format dates to compare them? What exactly
are
> you trying to do?
>
> ML|||Please stop thinking about the format. ***Format is irrelevant.*** You are
dealing with datetime values here, not strings. Not sure why you need to
print or select the date at all.
DECLARE @.dt SMALLDATETIME
SET @.dt = CURRENT_TIMESTAMP
-- "pass" all the rows, I have no idea what this means!
-- SELECT, maybe?
SELECT cols FROM table WHERE dt_column <= @.dt
DELETE table WHERE dt_column <= @.dt
ok?
"Ron Wallegie" <wallegie@.planet.nl> wrote in message
news:usyM%23bW4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> When starting a procedure iwant to pass all the records in a table before
> the getdate() value. After the procedure stops it deletes all value before
> the getdat value.
> I know there is a db setting which controls the format you get when
> rinting
> getdate(). I don't know how...
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:0F8F8DE5-4774-491B-A86E-41381F563B9F@.microsoft.com...
> are
>
sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
want to compare it to the current time. This doesn't work. When i print the
current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it returns
the current date as 02/10/2005 17:40:00.
Your help is much appreciated.
Regards Ron WallegiePrint converts to a string. What do you get when you say SELECT GETDATE()?
Different, no?
Keep in mind that I am having problems understanding your date for the very
reason that you chose an ambiguous date format. I suggest using YYYYMMDD or
YYYY-MM-DDTHH:MM:SS. These are ISO standards and are truthfully the only
safe formats you can use to represent dates in SQL Server. Anything else
can be misinterpreted by the database depending on regional settings,
dateformat, language, etc.
What do you mean by "compare it to the current time"? Compare what? A
value in a table?
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
>I have got an issue with dateformats. When i retrieve a date field from the
>sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
>want to compare it to the current time. This doesn't work. When i print the
>current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
>returns the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||If you want to compare two dates to see if they are the same.
SELECT
(CASE WHEN DATEDIFF( ms , myDateField , GETDATE() ) = 0 THEN 'Same' ELSE
'Different' END) AS DateCompare
FROM
myTable
If you're using SMALLDATETIME you can use DATEDIFF( mi , ... ), because it
is only accurate to a minute.
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I have got an issue with dateformats. When i retrieve a date field from
the
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print
the
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
returns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||Try using the convert function below.
By using the Convert function. The syntax for using the convert function
is: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
select convert(varchar,DateColumn,100) --> Nov 4 2005 11:08AM
or
I recommend converting both the date from the table and getdate()
using the function below in military time.
select convert(varchar,DateColumn,120) --> 2005-11-04 11:10:00
Select *
from table
where convert(varchar,DateColumn,120)=convert(
varchar,getdate(),120)
KT
"Ron Wallegie" <wallegie@.hotmail.com> wrote in message
news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I have got an issue with dateformats. When i retrieve a date field from
the
> sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
> want to compare it to the current time. This doesn't work. When i print
the
> current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it
returns
> the current date as 02/10/2005 17:40:00.
> Your help is much appreciated.
> Regards Ron Wallegie
>|||Hey Ron,
There are many ways to build up date strings, please google for them as MSDN
is quite limited in what it suggests. There is also a DB or Server setting
that controls this but I'm not sure what it is right now. Here are some
examples to get you started.
SELECT SUBSTRING(CONVERT(CHAR(12),GETDATE(),1),
1,8) + ' ' +
CONVERT(CHAR(12),GETDATE(),8)
select convert(smalldatetime,convert(char(12),g
etdate(),13))
select convert(datetime,convert(char(30),getdat
e(),21))
select convert(datetime,getdate(),10)|||Thx for your reply,
Hopefully this will help.
When printing getdate() i get " 4 Nov 2005 5:40PM". When retrieving the
table value i get 02/10/2005 17:40:00.
I created a script which works fine op a other server. When i do a select
getdate() on that server i get 02/10/2005 17:40:00 date format. How can i
change the format on the other server?
Regards Ron
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:exxqlFW4FHA.3292@.tk2msftngp13.phx.gbl...
> Print converts to a string. What do you get when you say SELECT
GETDATE()?
> Different, no?
> Keep in mind that I am having problems understanding your date for the
very
> reason that you chose an ambiguous date format. I suggest using YYYYMMDD
or
> YYYY-MM-DDTHH:MM:SS. These are ISO standards and are truthfully the only
> safe formats you can use to represent dates in SQL Server. Anything else
> can be misinterpreted by the database depending on regional settings,
> dateformat, language, etc.
> What do you mean by "compare it to the current time"? Compare what? A
> value in a table?
>
>
> "Ron Wallegie" <wallegie@.hotmail.com> wrote in message
> news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
the
the
>|||Just to clairify...
SET DATEFORMAT DMY -- i am assuming it's 2nd oct '05
DECLARE @.ClientDate SMALLDATETIME , @.TimeNow SMALLDATETIME
SELECT @.ClientDate = '02/10/2005 17:40:00' , @.TimeNow = GETDATE()
SELECT
@.ClientDate
, @.TimeNow
, (CASE
WHEN DATEDIFF( mi , @.ClientDate , @.TimeNow ) = 0 THEN 'Same'
ELSE 'Different'
END) AS DateCompare
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:436b8e34$0$140$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want to compare two dates to see if they are the same.
> SELECT
> (CASE WHEN DATEDIFF( ms , myDateField , GETDATE() ) = 0 THEN 'Same'
ELSE
> 'Different' END) AS DateCompare
> FROM
> myTable
> If you're using SMALLDATETIME you can use DATEDIFF( mi , ... ), because it
> is only accurate to a minute.
>
> "Ron Wallegie" <wallegie@.hotmail.com> wrote in message
> news:eWk8U$V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> the
> the
> returns
>|||Why do you think you need to format dates to compare them? What exactly are
you trying to do?
ML|||When starting a procedure iwant to pass all the records in a table before
the getdate() value. After the procedure stops it deletes all value before
the getdat value.
I know there is a db setting which controls the format you get when rinting
getdate(). I don't know how...
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0F8F8DE5-4774-491B-A86E-41381F563B9F@.microsoft.com...
> Why do you think you need to format dates to compare them? What exactly
are
> you trying to do?
>
> ML|||Please stop thinking about the format. ***Format is irrelevant.*** You are
dealing with datetime values here, not strings. Not sure why you need to
print or select the date at all.
DECLARE @.dt SMALLDATETIME
SET @.dt = CURRENT_TIMESTAMP
-- "pass" all the rows, I have no idea what this means!
-- SELECT, maybe?
SELECT cols FROM table WHERE dt_column <= @.dt
DELETE table WHERE dt_column <= @.dt
ok?
"Ron Wallegie" <wallegie@.planet.nl> wrote in message
news:usyM%23bW4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> When starting a procedure iwant to pass all the records in a table before
> the getdate() value. After the procedure stops it deletes all value before
> the getdat value.
> I know there is a db setting which controls the format you get when
> rinting
> getdate(). I don't know how...
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:0F8F8DE5-4774-491B-A86E-41381F563B9F@.microsoft.com...
> are
>
Subscribe to:
Posts (Atom)