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?
>
Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts
Thursday, March 29, 2012
Date question
Hello everybody i do have varchar column with DD-MM-YYYY. How can i convert
it to datetime data type ?
pls let me knowDo you need to jsut convert it within a query, you can use Cast of Convert
for that.
If you need to convert the column's data type, You have two options.
1) in T-SQL add the new column, do an update to move the data over, drop the
old column, then rename the new column
2) in Enterprise Manager change the data type and it will create a script
for you.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:BAD8F615-56DF-47EB-93A7-26A8AC5C0920@.microsoft.com...
> Hello everybody i do have varchar column with DD-MM-YYYY. How can i
> convert
> it to datetime data type ?
> pls let me know
it to datetime data type ?
pls let me knowDo you need to jsut convert it within a query, you can use Cast of Convert
for that.
If you need to convert the column's data type, You have two options.
1) in T-SQL add the new column, do an update to move the data over, drop the
old column, then rename the new column
2) in Enterprise Manager change the data type and it will create a script
for you.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:BAD8F615-56DF-47EB-93A7-26A8AC5C0920@.microsoft.com...
> Hello everybody i do have varchar column with DD-MM-YYYY. How can i
> convert
> it to datetime data type ?
> pls let me know
Tuesday, March 27, 2012
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
I have the following table with datetime and varchar(10) columns.
CREATE TABLE tblA (
fillDated datetime NULL ,
fillDate varchar (10)
)
Sample data:
fillDated fillDate
1/13/2006 1/13/2006
12/19/2005 12/19/2005
I would like to query those records where the date is >= today's date.
Say today's date is 1/13/2006.
From the above data, I would like the result to be
1/13/2006 1/13/2006
When I do the following
select tblA.filldated, tblA.filldate,getdate()
from tblA
where tblA.filldated >= getdate()
--> the result is NO record
when I do the following:
select tblA.filldated, tblA.filldate,getdate()
from tblA
where filldate >= convert(varchar(10),getdate(),101)
--> the result is
1/13/2006 1/13/2006
12/19/2005 12/19/2005 --> wrong, because 12/19/2005 is < 1/13/2006
How can I query the table so that I will get the following as the result ?
1/13/2006 1/13/2006
Thank you.filldated >= getdate() won't work because getdate() will return date
and time so getdate > filldated
filldate >= convert(varchar(10),getdate(),101) is comparing two
strings to see if they are >= then each other, not really what you want
to do is it?|||> filldate >= convert(varchar(10),getdate(),101) is comparing two
> strings to see if they are >= then each other, not really what you want
> to do is it?
No, I want the result to be
1/13/2006 1/13/2006
How can I do that ?
Thanks.
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137168331.130329.120930@.f14g2000cwb.googlegroups.com...
> filldated >= getdate() won't work because getdate() will return date
> and time so getdate > filldated
> filldate >= convert(varchar(10),getdate(),101) is comparing two
> strings to see if they are >= then each other, not really what you want
> to do is it?
>|||http://groups.google.com/group/micr...ring+&start=10&|||Thanks.
I solved the problem by using the following query:
select tblA.filldated, tblA.filldate,getdate()
from tblA
where datediff(day,getdate(),filldated) >= 0
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137170228.372663.107310@.g49g2000cwa.googlegroups.com...
> http://groups.google.com/group/micr...ring+&start=10&
>sql
CREATE TABLE tblA (
fillDated datetime NULL ,
fillDate varchar (10)
)
Sample data:
fillDated fillDate
1/13/2006 1/13/2006
12/19/2005 12/19/2005
I would like to query those records where the date is >= today's date.
Say today's date is 1/13/2006.
From the above data, I would like the result to be
1/13/2006 1/13/2006
When I do the following
select tblA.filldated, tblA.filldate,getdate()
from tblA
where tblA.filldated >= getdate()
--> the result is NO record
when I do the following:
select tblA.filldated, tblA.filldate,getdate()
from tblA
where filldate >= convert(varchar(10),getdate(),101)
--> the result is
1/13/2006 1/13/2006
12/19/2005 12/19/2005 --> wrong, because 12/19/2005 is < 1/13/2006
How can I query the table so that I will get the following as the result ?
1/13/2006 1/13/2006
Thank you.filldated >= getdate() won't work because getdate() will return date
and time so getdate > filldated
filldate >= convert(varchar(10),getdate(),101) is comparing two
strings to see if they are >= then each other, not really what you want
to do is it?|||> filldate >= convert(varchar(10),getdate(),101) is comparing two
> strings to see if they are >= then each other, not really what you want
> to do is it?
No, I want the result to be
1/13/2006 1/13/2006
How can I do that ?
Thanks.
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137168331.130329.120930@.f14g2000cwb.googlegroups.com...
> filldated >= getdate() won't work because getdate() will return date
> and time so getdate > filldated
> filldate >= convert(varchar(10),getdate(),101) is comparing two
> strings to see if they are >= then each other, not really what you want
> to do is it?
>|||http://groups.google.com/group/micr...ring+&start=10&|||Thanks.
I solved the problem by using the following query:
select tblA.filldated, tblA.filldate,getdate()
from tblA
where datediff(day,getdate(),filldated) >= 0
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137170228.372663.107310@.g49g2000cwa.googlegroups.com...
> http://groups.google.com/group/micr...ring+&start=10&
>sql
date prolem
I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect o
f
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function
.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dat
es
> in dd-mon-yyyy?
> thanks in advance|||
> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...[vbcol=seagreen]
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>|||Ooopps!
"Uri Dimant" wrote:
>
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
>
>sql
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect o
f
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function
.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dat
es
> in dd-mon-yyyy?
> thanks in advance|||
> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...[vbcol=seagreen]
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>|||Ooopps!
"Uri Dimant" wrote:
>
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
>
>sql
date prolem
I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect of
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dates
> in dd-mon-yyyy?
> thanks in advance|||> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>> I have created a table in which one column is datetime data type. But its
>> storing date andtimetogether. How can i chane the format to store only
>> dates
>> in dd-mon-yyyy?
>> thanks in advance|||Ooopps!
"Uri Dimant" wrote:
> > To expand on Hugo's post.
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> > Hi
> >
> > To expand on Hugo's post. The SQL Server datetime datatype will hold both
> > a
> > date and time. If you insert a date value with no time portion then the
> > time
> > will be defaulted to 00:00:00.000 and if you specify only a time the date
> > is
> > defaulted to
> > 1900-01-01. With SQL Server there is the CONVERT function that will format
> > a
> > datetime datetype value as a string. This will take a format specifier to
> > determine what format the string is. The getdate function will return the
> > current date and time, Hugo's second INSERT statement will use the FLOOR
> > function to truncate the time portion of a datetime data value, the effect
> > of
> > adding 1 to getdate will add one day. Therefore his code will add one day
> > to
> > the current date and time and then truncate the time portion.
> >
> > See Books Online for more on the datetime data type and the CONVERT
> > function.
> >
> > John
> >
> >
> > "Rajani" wrote:
> >
> >> I have created a table in which one column is datetime data type. But its
> >> storing date andtimetogether. How can i chane the format to store only
> >> dates
> >> in dd-mon-yyyy?
> >>
> >> thanks in advance
>
>
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect of
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dates
> in dd-mon-yyyy?
> thanks in advance|||> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>> I have created a table in which one column is datetime data type. But its
>> storing date andtimetogether. How can i chane the format to store only
>> dates
>> in dd-mon-yyyy?
>> thanks in advance|||Ooopps!
"Uri Dimant" wrote:
> > To expand on Hugo's post.
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> > Hi
> >
> > To expand on Hugo's post. The SQL Server datetime datatype will hold both
> > a
> > date and time. If you insert a date value with no time portion then the
> > time
> > will be defaulted to 00:00:00.000 and if you specify only a time the date
> > is
> > defaulted to
> > 1900-01-01. With SQL Server there is the CONVERT function that will format
> > a
> > datetime datetype value as a string. This will take a format specifier to
> > determine what format the string is. The getdate function will return the
> > current date and time, Hugo's second INSERT statement will use the FLOOR
> > function to truncate the time portion of a datetime data value, the effect
> > of
> > adding 1 to getdate will add one day. Therefore his code will add one day
> > to
> > the current date and time and then truncate the time portion.
> >
> > See Books Online for more on the datetime data type and the CONVERT
> > function.
> >
> > John
> >
> >
> > "Rajani" wrote:
> >
> >> I have created a table in which one column is datetime data type. But its
> >> storing date andtimetogether. How can i chane the format to store only
> >> dates
> >> in dd-mon-yyyy?
> >>
> >> thanks in advance
>
>
Sunday, March 25, 2012
Date problem
I have a table that has two datetime columns and the following stored
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks
Date problem
I have a table that has two datetime columns and the following stored
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks
procedure:
create proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
@.Impldate + N', ' + @.Implend + N')'
exec sp_executesql @.sql
go
When I execute this:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
'6/30/2006'
I do not get any errors, but the two date columns have 1/1/1900 instead of
the dates in quotes. Any Suggestions?
ThanksFirst, see what triggers are on that table that might be overriding the
dates you're inserting.
Second, in your procedure, comment out the line:
exec sp_executesql @.sql
Replace it with:
print @.sql
Run the exec statement that you provided:
exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST',
'6/1/2006', '6/30/2006'
Review the INSERT statement that is displayed, copy/paste it into QA
and run it.|||The datepart 1/6/2006 is evaluated to a integer division will will
result in zero, this is entered in the datetime column which is ther 0
day of the datetime column = 1/1/1900.
You should doublequote this to achieve the insert in the column,
something like this + '' + @.Variable + ''.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Try this stored procedure:
alter proc sp_BTInsertInitialValues
@.BankID nchar(3),
@.Librfacr nvarchar(3),
@.Modtrack nvarchar(3),
@.Fix_init_rate nvarchar(50),
@.Fix_init_match nvarchar(50),
@.Impldate nvarchar(10),
@.Implend nvarchar(10)
as
declare @.sql nvarchar(255)
set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
@.Librfacr + N', ' + @.Modtrack +
N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ''' +
@.Impldate + N''', ''' + @.Implend + N''')'
print @.sql
go
I think you are missing a quote for the dates.
Lucas
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks|||Thank you All.........
"DXC" wrote:
> I have a table that has two datetime columns and the following stored
> procedure:
> create proc sp_BTInsertInitialValues
> @.BankID nchar(3),
> @.Librfacr nvarchar(3),
> @.Modtrack nvarchar(3),
> @.Fix_init_rate nvarchar(50),
> @.Fix_init_match nvarchar(50),
> @.Impldate nvarchar(10),
> @.Implend nvarchar(10)
> as
> declare @.sql nvarchar(255)
> set @.sql = N'insert into InitialValues values(''' + @.BankID + N''', ' +
> @.Librfacr + N', ' + @.Modtrack +
> N', ''' + @.Fix_init_rate + N''', ''' + @.Fix_init_match + N''', ' +
> @.Impldate + N', ' + @.Implend + N')'
> exec sp_executesql @.sql
> go
> When I execute this:
> exec sp_BTInsertInitialValues 'RPT', '2', '4', 'TRES', 'TEST', '6/1/2006',
> '6/30/2006'
> I do not get any errors, but the two date columns have 1/1/1900 instead of
> the dates in quotes. Any Suggestions?
> Thanks
Date 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 picker parameter with MDX...
I am sure others have ran into this issue.
But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.
For those of you that are interested you merely need to format the parameter values coming into your dataset your passing the report params to.
Ex.
Code Snippet
="[COE Date].[Date].&["&Format(Parameters!FromCOEDateDate.Value,"yyyy-MM-dd")+"T00:00:00]"The above example shows the FromCOEDateDate parameter used in the report
Date part of DateTime
I'm trying this simple query in SQL Server 2005:
SELECT * FROM tblRecords
WHERE InputDate = '1/30/2007'
The table is already created and filled and I cant change anything in the table.
I'll use one of the above methods to get the result|||
SELECT * FROM tblRecords
WHERE InputDate = '1/30/2007'
I have two records which have value for InputDate as
1/30/2007 12:34:12
1/30/2007 11:23:32
But the query returns no records due to the time part of the date.
How do I pick only the date part for comparison and discard the time part in the InputDate ?
try this...
SELECT * FROM tblRecords
WHERE datediff(day,inputdate,'1/30/2007') = 0
You're right, apologies. It's been a long day. I've scrubbed my previous post.
Chris
:)
|||A few approaches:
This trick relies on the
fact that datetime values are represented internally as floating point
values. The integer part represents the date, and the fractional part
the time. So rounding down to the nearest integer value gives you a
date.
DECLARE @.d datetime
SET @.d =
GETDATE()
SELECT @.d
SELECT CAST(FLOOR(CAST(@.d AS
float)) AS datetime)
However, doing the CAST/FLOOR
trick on a column name will probably prevent the optimizer from using
indexes on that column. This is more useful when you want to get just
the date part of GETDATE() and check it against date-only values in a
column.
In this case, you can
probably do something like this:
SELECT * FROM
tblRecords
WHERE InputDate >= '1/30/2007' AND InputDate
< '2/1/2007'
Or if you're comparing against a
variable, this would probably do the trick:
SELECT * FROM
tblRecords
WHERE InputDate >= @.idate AND InputDate
< DATEADD(dy, 1, @.idate)
|||I prefer David's reply:
SELECT * FROM tblRecords
WHERE InputDate >= '1/30/2007' AND InputDate < '2/1/2007'
This method can take advantage of potential indexes that might be available on the inputDate column. Note that in the following mockup that David's method gets an INDEX SEEK plan whereas the plans of the queries that enclose a function around the inputDate field will use either a CLUSTERED INDEX or a TABLE SCAN:
|||Thank you all for the help provided.create table dbo.tblRecords
( rid integer not null
constraint pk_tblRecords primary key,
inputDate datetime not null,
filler char (300) not null
)
gocreate index inputDate on dbo.tblRecords(inputDate)
goupdate statistics dbo.tblRecords
goinsert into dbo.tblRecords
select iter,
cast(cast(cast('3/15/5' as datetime) as float)
+ 730 * dbo.rand() as datetime),
'Record # ' + convert(varchar(5), iter)
from small_iterator (nolock)go
--set showplan_text on
goselect *
from tblRecords
where inputDate >= '1/30/2007'
and inputDate < '1/31/2007'go
--set showplan_text off
go-- - 47 Rows Returned -
- rid inputDate filler
-- -- -
-- 4400 2007-01-30 00:08:11.790 Record # 4400
-- 10836 2007-01-30 00:27:30.883 Record # 10836
-- ...
-- 21253 2007-01-30 10:14:39.810 Record # 21253
-- 7795 2007-01-30 11:07:24.507 Record # 7795
-- StmtText
-- --
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[tblRecords]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[tblRecords].[inputDate]), SEEK:([tblRecords].[inputDate] >= Convert([@.1]) AND [tblRecords].[inputDate] < Convert([@.2])) ORDERED FORWARD)-- Table 'tblRecords'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 0.
SELECT * FROM tblRecords
WHERE datediff(day,inputdate,'1/30/2007') = 0StmtText
-- -
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[tblRecords].[pk_tblRecords]), WHERE:(datediff(day, [tblRecords].[inputDate], 'Jan 30 2007 12:00AM')=0))-- Table 'tblRecords'. Scan count 1, logical reads 1313, physical reads 0, read-ahead reads 0.
The table is already created and filled and I cant change anything in the table.
I'll use one of the above methods to get the result|||
Some
time it is required to use Convert even though it decrease the performance
SELECT * FROM tblRecords
WHERE convert(Varchar(10),InputDate,101) = '30/01/2007'
Date part of a dateTime value
Hi,
This is no function in SQL Server to get only the date part of a datetime
variable, sometime, and usually most of the time, I would like to have a
query to get the result of only some specific day's, and I am not sure what
could be the best way to do this query.
For example, if I want to get all the records of my table for only todays.
Thanks in advance for your advice on this.
FrankYou can get the Date part of the date time using the datepart function.
select Convert(nvarchar(25),getdate(),100 )
You can use the datediff function for retrieving the current day's
record
Select * from table where datediff(d,getdate(),datecolumn) = 0|||Hi,
SELECT * from YOurTable
Where Datefield >= convert(VARCHAR(8),getdate(),112)
Uses an ISO Date format, you could also Convert the Datefield Column, but
this wont need to be neccessary because of causing performance issues.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
> Frank
>|||Continuation to the above message from me ...
Sorry .. use this
select Convert(nvarchar(25),getdate(),101 )
you can also check books online for the different values instead of 101
for getting the required output|||Thanks, balacr,
The second datediff(d, getdate(), datecolumn) = 0 is exactly what I want.
B/R
Frank
<balacr@.gmail.com> wrote in message
news:1113806479.336151.291970@.l41g2000cwc.googlegroups.com...
> You can get the Date part of the date time using the datepart function.
> select Convert(nvarchar(25),getdate(),100 )
> You can use the datediff function for retrieving the current day's
> record
> Select * from table where datediff(d,getdate(),datecolumn) = 0
>|||Hi Frank,
"Frank" <wangping@.lucent.com> wrote in message
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
Use Northwind
/* (1) */
select * from orders
where
orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
DATETIME))
/* (2) */
select * from orders
where
day(orderdate) = day(getdate()) and
month(orderdate) = month(getdate()) and
year(orderdate) = year(getdate())
/* (3) */
Select * from orders
where
datediff(day ,getdate() , orderdate) = 0
> Frank
HTH,
Andrea|||Thanks, Jens,
I also noticed that the "where datediff()" method could causing performance
issues, since I was told that if in the "where clause", there is some
functions on the column, then even there is an index on that column, the
database query engine will not use it.
That is something like this,
select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
Then even on "myDateColumn" there is an index, the SQL Server will not use
that index.
Am I right?
B/R
Frank
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> SELECT * from YOurTable
> Where Datefield >= convert(VARCHAR(8),getdate(),112)
> Uses an ISO Date format, you could also Convert the Datefield Column, but
> this wont need to be neccessary because of causing performance issues.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
>|||Yeah you are, thats why i pointed out not to do something with the column
rather than doing this "static" Conversion (because it will be only
evaluated once.)
HTH, Jens Smeyer.
http:/www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:uKcZMQ%23QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thanks, Jens,
> I also noticed that the "where datediff()" method could causing
> performance
> issues, since I was told that if in the "where clause", there is some
> functions on the column, then even there is an index on that column, the
> database query engine will not use it.
> That is something like this,
> select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
> Then even on "myDateColumn" there is an index, the SQL Server will not use
> that index.
> Am I right?
> B/R
> Frank
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> datetime
> todays.
>|||Thanks, Andrea,
I would like to choose the first of your ideas, I think there maybe some
performance issues for the second and third ones.
B/R
Frank
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:%232XsyM%23QFHA.3076@.tk2msftngp13.phx.gbl...
> Hi Frank,
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
> Use Northwind
> /* (1) */
> select * from orders
> where
> orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
> orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
> DATETIME))
> /* (2) */
> select * from orders
> where
> day(orderdate) = day(getdate()) and
> month(orderdate) = month(getdate()) and
> year(orderdate) = year(getdate())
> /* (3) */
> Select * from orders
> where
> datediff(day ,getdate() , orderdate) = 0
>
> HTH,
> Andrea
>sql
This is no function in SQL Server to get only the date part of a datetime
variable, sometime, and usually most of the time, I would like to have a
query to get the result of only some specific day's, and I am not sure what
could be the best way to do this query.
For example, if I want to get all the records of my table for only todays.
Thanks in advance for your advice on this.
FrankYou can get the Date part of the date time using the datepart function.
select Convert(nvarchar(25),getdate(),100 )
You can use the datediff function for retrieving the current day's
record
Select * from table where datediff(d,getdate(),datecolumn) = 0|||Hi,
SELECT * from YOurTable
Where Datefield >= convert(VARCHAR(8),getdate(),112)
Uses an ISO Date format, you could also Convert the Datefield Column, but
this wont need to be neccessary because of causing performance issues.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
> Frank
>|||Continuation to the above message from me ...
Sorry .. use this
select Convert(nvarchar(25),getdate(),101 )
you can also check books online for the different values instead of 101
for getting the required output|||Thanks, balacr,
The second datediff(d, getdate(), datecolumn) = 0 is exactly what I want.
B/R
Frank
<balacr@.gmail.com> wrote in message
news:1113806479.336151.291970@.l41g2000cwc.googlegroups.com...
> You can get the Date part of the date time using the datepart function.
> select Convert(nvarchar(25),getdate(),100 )
> You can use the datediff function for retrieving the current day's
> record
> Select * from table where datediff(d,getdate(),datecolumn) = 0
>|||Hi Frank,
"Frank" <wangping@.lucent.com> wrote in message
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
Use Northwind
/* (1) */
select * from orders
where
orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
DATETIME))
/* (2) */
select * from orders
where
day(orderdate) = day(getdate()) and
month(orderdate) = month(getdate()) and
year(orderdate) = year(getdate())
/* (3) */
Select * from orders
where
datediff(day ,getdate() , orderdate) = 0
> Frank
HTH,
Andrea|||Thanks, Jens,
I also noticed that the "where datediff()" method could causing performance
issues, since I was told that if in the "where clause", there is some
functions on the column, then even there is an index on that column, the
database query engine will not use it.
That is something like this,
select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
Then even on "myDateColumn" there is an index, the SQL Server will not use
that index.
Am I right?
B/R
Frank
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> SELECT * from YOurTable
> Where Datefield >= convert(VARCHAR(8),getdate(),112)
> Uses an ISO Date format, you could also Convert the Datefield Column, but
> this wont need to be neccessary because of causing performance issues.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
>|||Yeah you are, thats why i pointed out not to do something with the column
rather than doing this "static" Conversion (because it will be only
evaluated once.)
HTH, Jens Smeyer.
http:/www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:uKcZMQ%23QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thanks, Jens,
> I also noticed that the "where datediff()" method could causing
> performance
> issues, since I was told that if in the "where clause", there is some
> functions on the column, then even there is an index on that column, the
> database query engine will not use it.
> That is something like this,
> select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
> Then even on "myDateColumn" there is an index, the SQL Server will not use
> that index.
> Am I right?
> B/R
> Frank
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> datetime
> todays.
>|||Thanks, Andrea,
I would like to choose the first of your ideas, I think there maybe some
performance issues for the second and third ones.
B/R
Frank
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:%232XsyM%23QFHA.3076@.tk2msftngp13.phx.gbl...
> Hi Frank,
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
> Use Northwind
> /* (1) */
> select * from orders
> where
> orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
> orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
> DATETIME))
> /* (2) */
> select * from orders
> where
> day(orderdate) = day(getdate()) and
> month(orderdate) = month(getdate()) and
> year(orderdate) = year(getdate())
> /* (3) */
> Select * from orders
> where
> datediff(day ,getdate() , orderdate) = 0
>
> HTH,
> Andrea
>sql
Thursday, March 22, 2012
Date parameters in ="select.......where creationdate="&...."
Hi there,
I'm looking for the correct syntax for using datetime parameters in the SQL
statement.
The parameter is called ReceiveDate and defaults to =Today()
="select ...... where creationdate = "&.....
Thanks
LudoWhere you are writing the statemnt? In report or in stored procedure ?
If it is in report itself you can do as given belo:
If you declare ReceiveDate as DateTime Type. you can write the sql statement
directly .
Select ...... Where creationdate = @.ReceiveDate
If you Declare it as String then you need to use date conversion function
Select ...... Where creationdate = CDate(@.ReceiveDate)|||I think the issue here is that you are using an expression which is really a
mistake unless there is no other way. If you use the generic query designer
(two pane) then just do this:
select ... where creationdate = @.ReceiveDate
You don't have to do anything special. RS takes care of everything. If you
use an expression then you have to do a lot more (for instance, embed single
quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm looking for the correct syntax for using datetime parameters in the
> SQL statement.
> The parameter is called ReceiveDate and defaults to =Today()
> ="select ...... where creationdate = "&.....
> Thanks
> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>I think the issue here is that you are using an expression which is really
>a mistake unless there is no other way. If you use the generic query
>designer (two pane) then just do this:
> select ... where creationdate = @.ReceiveDate
> You don't have to do anything special. RS takes care of everything. If you
> use an expression then you have to do a lot more (for instance, embed
> single quotes).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Rama Prasad" <RamaPrasad@.discussions.microsoft.com> wrote in message
news:827DCC76-9E60-40EF-9065-EBB1D9B4A335@.microsoft.com...
> Where you are writing the statemnt? In report or in stored procedure ?
> If it is in report itself you can do as given belo:
> If you declare ReceiveDate as DateTime Type. you can write the sql
> statement
> directly .
> Select ...... Where creationdate = @.ReceiveDate
> If you Declare it as String then you need to use date conversion function
> Select ...... Where creationdate = CDate(@.ReceiveDate)
>|||This is very odd. RS should be handling this. There is no reason for you to
have to use CDate or anything like that. Try changing your SQL to be like
this:
select part_number,serial_number from UNIT_STATUS_V where creation_time >=@.StartDate and creation_time <= @.EndDate
You definitely should be able to have the parameter be a datetime.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> it is in the report (RS 2005). It works when I put the parameter to string
> and use query:
> select part_number,serial_number from UNIT_STATUS_V where creation_time
> between @.StartDate and @.EndDate
> creation_time is a DateTime field.
> But I'd rather use the parameter as a DateTime as well since then I get a
> calender pick control.
> I tried using CDate but with no result so far...
> Ludo
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is really
>>a mistake unless there is no other way. If you use the generic query
>>designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance, embed
>> single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>|||Hi again....
thanks for your help so far, I found the problem and I'll try to explain.
My date settings are set to british "dd/MM/yyyy". When I do the preview of
the report the EndDate parameter defaults to =DateAdd("d",-1,Today()) and
the StartDate to =Today(). The readable fields read 19/10/2005 and
20/10/2005 which look fine. When I click on the preview tab the report runs
but when I click on view report the system returns an erro on the date
parameter. Now...when I select a date in the calender picker the readable
fields shows the date correctly like dd/MM/yyyy but the report returns the
same error. But when I select fi 08/08/2005 till 09/08/2005 and I click
view report the report runs and the 2 visible fields previously filled by
the calender picker change to 08/08/2005 till 08/09/2005 so the reports runs
from august 8th till september 8th ...I don't know the reason but when I
deploy the report it seems to run correctly, so the problem only occurs in
the preview....
Could this be a bug in the RS 2005 or do you know some settings I need to
check ?
Anyway many thanks for your help.
Ludo Van Dun
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMgscQL1FHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is very odd. RS should be handling this. There is no reason for you
> to have to use CDate or anything like that. Try changing your SQL to be
> like this:
> select part_number,serial_number from UNIT_STATUS_V where creation_time >=> @.StartDate and creation_time <= @.EndDate
> You definitely should be able to have the parameter be a datetime.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> it is in the report (RS 2005). It works when I put the parameter to
>> string and use query:
>> select part_number,serial_number from UNIT_STATUS_V where creation_time
>> between @.StartDate and @.EndDate
>> creation_time is a DateTime field.
>> But I'd rather use the parameter as a DateTime as well since then I get a
>> calender pick control.
>> I tried using CDate but with no result so far...
>> Ludo
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is
>>really a mistake unless there is no other way. If you use the generic
>>query designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance,
>> embed single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>>
>
I'm looking for the correct syntax for using datetime parameters in the SQL
statement.
The parameter is called ReceiveDate and defaults to =Today()
="select ...... where creationdate = "&.....
Thanks
LudoWhere you are writing the statemnt? In report or in stored procedure ?
If it is in report itself you can do as given belo:
If you declare ReceiveDate as DateTime Type. you can write the sql statement
directly .
Select ...... Where creationdate = @.ReceiveDate
If you Declare it as String then you need to use date conversion function
Select ...... Where creationdate = CDate(@.ReceiveDate)|||I think the issue here is that you are using an expression which is really a
mistake unless there is no other way. If you use the generic query designer
(two pane) then just do this:
select ... where creationdate = @.ReceiveDate
You don't have to do anything special. RS takes care of everything. If you
use an expression then you have to do a lot more (for instance, embed single
quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm looking for the correct syntax for using datetime parameters in the
> SQL statement.
> The parameter is called ReceiveDate and defaults to =Today()
> ="select ...... where creationdate = "&.....
> Thanks
> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>I think the issue here is that you are using an expression which is really
>a mistake unless there is no other way. If you use the generic query
>designer (two pane) then just do this:
> select ... where creationdate = @.ReceiveDate
> You don't have to do anything special. RS takes care of everything. If you
> use an expression then you have to do a lot more (for instance, embed
> single quotes).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Rama Prasad" <RamaPrasad@.discussions.microsoft.com> wrote in message
news:827DCC76-9E60-40EF-9065-EBB1D9B4A335@.microsoft.com...
> Where you are writing the statemnt? In report or in stored procedure ?
> If it is in report itself you can do as given belo:
> If you declare ReceiveDate as DateTime Type. you can write the sql
> statement
> directly .
> Select ...... Where creationdate = @.ReceiveDate
> If you Declare it as String then you need to use date conversion function
> Select ...... Where creationdate = CDate(@.ReceiveDate)
>|||This is very odd. RS should be handling this. There is no reason for you to
have to use CDate or anything like that. Try changing your SQL to be like
this:
select part_number,serial_number from UNIT_STATUS_V where creation_time >=@.StartDate and creation_time <= @.EndDate
You definitely should be able to have the parameter be a datetime.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> it is in the report (RS 2005). It works when I put the parameter to string
> and use query:
> select part_number,serial_number from UNIT_STATUS_V where creation_time
> between @.StartDate and @.EndDate
> creation_time is a DateTime field.
> But I'd rather use the parameter as a DateTime as well since then I get a
> calender pick control.
> I tried using CDate but with no result so far...
> Ludo
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is really
>>a mistake unless there is no other way. If you use the generic query
>>designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance, embed
>> single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>|||Hi again....
thanks for your help so far, I found the problem and I'll try to explain.
My date settings are set to british "dd/MM/yyyy". When I do the preview of
the report the EndDate parameter defaults to =DateAdd("d",-1,Today()) and
the StartDate to =Today(). The readable fields read 19/10/2005 and
20/10/2005 which look fine. When I click on the preview tab the report runs
but when I click on view report the system returns an erro on the date
parameter. Now...when I select a date in the calender picker the readable
fields shows the date correctly like dd/MM/yyyy but the report returns the
same error. But when I select fi 08/08/2005 till 09/08/2005 and I click
view report the report runs and the 2 visible fields previously filled by
the calender picker change to 08/08/2005 till 08/09/2005 so the reports runs
from august 8th till september 8th ...I don't know the reason but when I
deploy the report it seems to run correctly, so the problem only occurs in
the preview....
Could this be a bug in the RS 2005 or do you know some settings I need to
check ?
Anyway many thanks for your help.
Ludo Van Dun
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMgscQL1FHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is very odd. RS should be handling this. There is no reason for you
> to have to use CDate or anything like that. Try changing your SQL to be
> like this:
> select part_number,serial_number from UNIT_STATUS_V where creation_time >=> @.StartDate and creation_time <= @.EndDate
> You definitely should be able to have the parameter be a datetime.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> it is in the report (RS 2005). It works when I put the parameter to
>> string and use query:
>> select part_number,serial_number from UNIT_STATUS_V where creation_time
>> between @.StartDate and @.EndDate
>> creation_time is a DateTime field.
>> But I'd rather use the parameter as a DateTime as well since then I get a
>> calender pick control.
>> I tried using CDate but with no result so far...
>> Ludo
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is
>>really a mistake unless there is no other way. If you use the generic
>>query designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance,
>> embed single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>>
>
Date Parameter without time
In the Report Parameters dialog I define my Parameter and set
Data Type to Datetime.
When I run report I get 22/2/2005 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to appear and make input like 22/2/2005
ie.(dd/MM/yyyy) and without time part.
Thanks in advanceI use a string for all of my dates and it works fine. In my dataset or
SP that is called, I simply format the date and add 00:00:00 to the
start date, and 23:59:59 to the end date.|||Or you could use a custom code to return a short date string for that
field.
call Code.FormatDate(Fields!data.Value)
Function FormatDate(ByVal date as Object) as String
If IsDate(data) Then
Return CDate(date).ToShortDateString()
Else
Return String.Empty
End If
End Funtion
Data Type to Datetime.
When I run report I get 22/2/2005 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to appear and make input like 22/2/2005
ie.(dd/MM/yyyy) and without time part.
Thanks in advanceI use a string for all of my dates and it works fine. In my dataset or
SP that is called, I simply format the date and add 00:00:00 to the
start date, and 23:59:59 to the end date.|||Or you could use a custom code to return a short date string for that
field.
call Code.FormatDate(Fields!data.Value)
Function FormatDate(ByVal date as Object) as String
If IsDate(data) Then
Return CDate(date).ToShortDateString()
Else
Return String.Empty
End If
End Funtion
Date Parameter Labels?
I have the usual report with date range parameters (from, to) that I have set
up as datetime format so that users can get the fancy calendar applet. thats
all fine and good. My problem is that I have creative users that like to hand
enter all kinds of date values other than the expected <mm/dd/yyyy> values
and are surprised that they dont return results.
I know I can create code in my sproc to handle all of the various
permutations on the backend, and that is probably the more "user friendly?
way to go, but what I want to know is if there is a way to put a label in the
parameter boxes that will show the expected format. I tried messing with
default parameters, but that didnt work when using a string as a default for
a datetime parameter. All I want is for the lable to show the expected
format, not act as a default value in any way.
If anyone has any ideas, please let me know. Perhaps this will be
functionality in the next version of RS?
Thanks!You can already put whatever label you want for the parameter. In layout
mode, Report Menu->Report Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>I have the usual report with date range parameters (from, to) that I have
>set
> up as datetime format so that users can get the fancy calendar applet.
> thats
> all fine and good. My problem is that I have creative users that like to
> hand
> enter all kinds of date values other than the expected <mm/dd/yyyy> values
> and are surprised that they dont return results.
> I know I can create code in my sproc to handle all of the various
> permutations on the backend, and that is probably the more "user friendly?
> way to go, but what I want to know is if there is a way to put a label in
> the
> parameter boxes that will show the expected format. I tried messing with
> default parameters, but that didnt work when using a string as a default
> for
> a datetime parameter. All I want is for the lable to show the expected
> format, not act as a default value in any way.
> If anyone has any ideas, please let me know. Perhaps this will be
> functionality in the next version of RS?
> Thanks!|||Different kind of label.
I know I can put the format in the NAME of the parameter, but this looks
kind of ugly and is only a last resort. I want to put the format INSIDE the
text box, much like a default value, but have it disappears when typed over
or the calendar applet is used. I dont want it seen by the reporting server
as an actual default value.
"Bruce L-C [MVP]" wrote:
> You can already put whatever label you want for the parameter. In layout
> mode, Report Menu->Report Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
> >I have the usual report with date range parameters (from, to) that I have
> >set
> > up as datetime format so that users can get the fancy calendar applet.
> > thats
> > all fine and good. My problem is that I have creative users that like to
> > hand
> > enter all kinds of date values other than the expected <mm/dd/yyyy> values
> > and are surprised that they dont return results.
> > I know I can create code in my sproc to handle all of the various
> > permutations on the backend, and that is probably the more "user friendly?
> > way to go, but what I want to know is if there is a way to put a label in
> > the
> > parameter boxes that will show the expected format. I tried messing with
> > default parameters, but that didnt work when using a string as a default
> > for
> > a datetime parameter. All I want is for the lable to show the expected
> > format, not act as a default value in any way.
> >
> > If anyone has any ideas, please let me know. Perhaps this will be
> > functionality in the next version of RS?
> > Thanks!
>
>|||Ahhh, I see. Nope, can't do it. However, if they put in a non-date format
and you have the parameter value as date then RS will tell the user. Not the
best but not the worst error message. For instance: The value provided for
the report parameter 'FromDate' is not valid for its type
I have found most people use a date picker if it is there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0F768D55-C02B-4A3E-BBCA-2EA618B0490F@.microsoft.com...
> Different kind of label.
> I know I can put the format in the NAME of the parameter, but this looks
> kind of ugly and is only a last resort. I want to put the format INSIDE
> the
> text box, much like a default value, but have it disappears when typed
> over
> or the calendar applet is used. I dont want it seen by the reporting
> server
> as an actual default value.
> "Bruce L-C [MVP]" wrote:
>> You can already put whatever label you want for the parameter. In layout
>> mode, Report Menu->Report Parameters
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>> >I have the usual report with date range parameters (from, to) that I
>> >have
>> >set
>> > up as datetime format so that users can get the fancy calendar applet.
>> > thats
>> > all fine and good. My problem is that I have creative users that like
>> > to
>> > hand
>> > enter all kinds of date values other than the expected <mm/dd/yyyy>
>> > values
>> > and are surprised that they dont return results.
>> > I know I can create code in my sproc to handle all of the various
>> > permutations on the backend, and that is probably the more "user
>> > friendly?
>> > way to go, but what I want to know is if there is a way to put a label
>> > in
>> > the
>> > parameter boxes that will show the expected format. I tried messing
>> > with
>> > default parameters, but that didnt work when using a string as a
>> > default
>> > for
>> > a datetime parameter. All I want is for the lable to show the expected
>> > format, not act as a default value in any way.
>> >
>> > If anyone has any ideas, please let me know. Perhaps this will be
>> > functionality in the next version of RS?
>> > Thanks!
>>
up as datetime format so that users can get the fancy calendar applet. thats
all fine and good. My problem is that I have creative users that like to hand
enter all kinds of date values other than the expected <mm/dd/yyyy> values
and are surprised that they dont return results.
I know I can create code in my sproc to handle all of the various
permutations on the backend, and that is probably the more "user friendly?
way to go, but what I want to know is if there is a way to put a label in the
parameter boxes that will show the expected format. I tried messing with
default parameters, but that didnt work when using a string as a default for
a datetime parameter. All I want is for the lable to show the expected
format, not act as a default value in any way.
If anyone has any ideas, please let me know. Perhaps this will be
functionality in the next version of RS?
Thanks!You can already put whatever label you want for the parameter. In layout
mode, Report Menu->Report Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>I have the usual report with date range parameters (from, to) that I have
>set
> up as datetime format so that users can get the fancy calendar applet.
> thats
> all fine and good. My problem is that I have creative users that like to
> hand
> enter all kinds of date values other than the expected <mm/dd/yyyy> values
> and are surprised that they dont return results.
> I know I can create code in my sproc to handle all of the various
> permutations on the backend, and that is probably the more "user friendly?
> way to go, but what I want to know is if there is a way to put a label in
> the
> parameter boxes that will show the expected format. I tried messing with
> default parameters, but that didnt work when using a string as a default
> for
> a datetime parameter. All I want is for the lable to show the expected
> format, not act as a default value in any way.
> If anyone has any ideas, please let me know. Perhaps this will be
> functionality in the next version of RS?
> Thanks!|||Different kind of label.
I know I can put the format in the NAME of the parameter, but this looks
kind of ugly and is only a last resort. I want to put the format INSIDE the
text box, much like a default value, but have it disappears when typed over
or the calendar applet is used. I dont want it seen by the reporting server
as an actual default value.
"Bruce L-C [MVP]" wrote:
> You can already put whatever label you want for the parameter. In layout
> mode, Report Menu->Report Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
> >I have the usual report with date range parameters (from, to) that I have
> >set
> > up as datetime format so that users can get the fancy calendar applet.
> > thats
> > all fine and good. My problem is that I have creative users that like to
> > hand
> > enter all kinds of date values other than the expected <mm/dd/yyyy> values
> > and are surprised that they dont return results.
> > I know I can create code in my sproc to handle all of the various
> > permutations on the backend, and that is probably the more "user friendly?
> > way to go, but what I want to know is if there is a way to put a label in
> > the
> > parameter boxes that will show the expected format. I tried messing with
> > default parameters, but that didnt work when using a string as a default
> > for
> > a datetime parameter. All I want is for the lable to show the expected
> > format, not act as a default value in any way.
> >
> > If anyone has any ideas, please let me know. Perhaps this will be
> > functionality in the next version of RS?
> > Thanks!
>
>|||Ahhh, I see. Nope, can't do it. However, if they put in a non-date format
and you have the parameter value as date then RS will tell the user. Not the
best but not the worst error message. For instance: The value provided for
the report parameter 'FromDate' is not valid for its type
I have found most people use a date picker if it is there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0F768D55-C02B-4A3E-BBCA-2EA618B0490F@.microsoft.com...
> Different kind of label.
> I know I can put the format in the NAME of the parameter, but this looks
> kind of ugly and is only a last resort. I want to put the format INSIDE
> the
> text box, much like a default value, but have it disappears when typed
> over
> or the calendar applet is used. I dont want it seen by the reporting
> server
> as an actual default value.
> "Bruce L-C [MVP]" wrote:
>> You can already put whatever label you want for the parameter. In layout
>> mode, Report Menu->Report Parameters
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>> >I have the usual report with date range parameters (from, to) that I
>> >have
>> >set
>> > up as datetime format so that users can get the fancy calendar applet.
>> > thats
>> > all fine and good. My problem is that I have creative users that like
>> > to
>> > hand
>> > enter all kinds of date values other than the expected <mm/dd/yyyy>
>> > values
>> > and are surprised that they dont return results.
>> > I know I can create code in my sproc to handle all of the various
>> > permutations on the backend, and that is probably the more "user
>> > friendly?
>> > way to go, but what I want to know is if there is a way to put a label
>> > in
>> > the
>> > parameter boxes that will show the expected format. I tried messing
>> > with
>> > default parameters, but that didnt work when using a string as a
>> > default
>> > for
>> > a datetime parameter. All I want is for the lable to show the expected
>> > format, not act as a default value in any way.
>> >
>> > If anyone has any ideas, please let me know. Perhaps this will be
>> > functionality in the next version of RS?
>> > Thanks!
>>
Date parameter 7 days in advance
can somebody pls help me with the formula for a datetime parameter that is
say 7 days past today. this will be used as the end date parameter.
my start_date parameter default is =(today)
thanksTry using SQL to create a dataset.
SELECT dateadd(day,7,getdate()) as end_date
I use dateadd like crazy.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:Tango@.discussions.microsoft.com:
> can somebody pls help me with the formula for a datetime parameter that is
> say 7 days past today. this will be used as the end date parameter.
> my start_date parameter default is =(today)
> thanks|||Thanks John,
I tried adding this statement in the default value of the query & it didnt
work
I also copied your statement into the query string of a new dataset & get
error message 'minimum capacity must be non negative'
Todd
"John Geddes" wrote:
> Try using SQL to create a dataset.
> SELECT dateadd(day,7,getdate()) as end_date
> I use dateadd like crazy.
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:Tango@.discussions.microsoft.com:
> > can somebody pls help me with the formula for a datetime parameter that is
> >
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks
>
>|||undefined function 'getdate' in expression is the error message i get when i
run the query in the new dataset
"John Geddes" wrote:
> Put the statement in a new dataset.
> Then, go to the parameter and select default value from dataset, pick
> your new dataset, and then pick the column name.
> Did that help?
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:Tango@.discussions.microsoft.com:
> > Thanks John,
> >
> > I tried adding this statement in the default value of the query & it didnt
> >
> > work
> > I also copied your statement into the query string of a new dataset & get
> >
> > error message 'minimum capacity must be non negative'
> >
> > Todd
> >
> > "John Geddes" wrote:
> >
> > > Try using SQL to create a dataset.
> > >
> > > SELECT dateadd(day,7,getdate()) as end_date
> > >
> > > I use dateadd like crazy.
> > >
> > >
> > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > news:Tango@.discussions.microsoft.com:
> > > > can somebody pls help me with the formula for a datetime parameter
> > > > that is
> > > >
> > > > say 7 days past today. this will be used as the end date parameter.
> > > >
> > > > my start_date parameter default is =(today)
> > > >
> > > > thanks
> > >
> > >
> > >
>
>|||Thanks saglamtimur
i am now getting an error message 'doesnt have the expected type'
"saglamtimur" wrote:
> If you want vb.net solution I use this function for one week (7 days);
> =format(dateadd("ww",1,Globals!ExecutionTime),"dd/MM/yyyy")
> "ww" equals week, 1 equals 1 week, if you want further info just google
> "vb.net dateadd function"
> Hope helps.
> Regards
> "John Geddes" <john_g@.alamode.com> wrote in message
> news:#KqRU#h4EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Put the statement in a new dataset.
> >
> > Then, go to the parameter and select default value from dataset, pick
> > your new dataset, and then pick the column name.
> >
> > Did that help?
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:Tango@.discussions.microsoft.com:
> > > Thanks John,
> > >
> > > I tried adding this statement in the default value of the query & it
> didnt
> > >
> > > work
> > > I also copied your statement into the query string of a new dataset &
> get
> > >
> > > error message 'minimum capacity must be non negative'
> > >
> > > Todd
> > >
> > > "John Geddes" wrote:
> > >
> > > > Try using SQL to create a dataset.
> > > >
> > > > SELECT dateadd(day,7,getdate()) as end_date
> > > >
> > > > I use dateadd like crazy.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:Tango@.discussions.microsoft.com:
> > > > > can somebody pls help me with the formula for a datetime parameter
> > > > > that is
> > > > >
> > > > > say 7 days past today. this will be used as the end date parameter.
> > > > >
> > > > > my start_date parameter default is =(today)
> > > > >
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
>
>|||Use expression (for seven days before):
=DateTime.Now.AddDays(-7)
Use expression (for seven days after):
=DateTime.Now.AddDays(7)
"Tango" wrote:
> can somebody pls help me with the formula for a datetime parameter that is
> say 7 days past today. this will be used as the end date parameter.
> my start_date parameter default is =(today)
> thanks|||Thank you soooo much sathya
works a charm
"sathya" wrote:
> Use expression (for seven days before):
> =DateTime.Now.AddDays(-7)
> Use expression (for seven days after):
> =DateTime.Now.AddDays(7)
>
> "Tango" wrote:
> > can somebody pls help me with the formula for a datetime parameter that is
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks|||How could this be done w/ a parameter?
=format(dateadd("mm",-1,parameters!stardate),"MMMM")
Thanks...
"saglamtimur" wrote:
> If you want vb.net solution I use this function for one week (7 days);
> =format(dateadd("ww",1,Globals!ExecutionTime),"dd/MM/yyyy")
> "ww" equals week, 1 equals 1 week, if you want further info just google
> "vb.net dateadd function"
> Hope helps.
> Regards
> "John Geddes" <john_g@.alamode.com> wrote in message
> news:#KqRU#h4EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Put the statement in a new dataset.
> >
> > Then, go to the parameter and select default value from dataset, pick
> > your new dataset, and then pick the column name.
> >
> > Did that help?
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:Tango@.discussions.microsoft.com:
> > > Thanks John,
> > >
> > > I tried adding this statement in the default value of the query & it
> didnt
> > >
> > > work
> > > I also copied your statement into the query string of a new dataset &
> get
> > >
> > > error message 'minimum capacity must be non negative'
> > >
> > > Todd
> > >
> > > "John Geddes" wrote:
> > >
> > > > Try using SQL to create a dataset.
> > > >
> > > > SELECT dateadd(day,7,getdate()) as end_date
> > > >
> > > > I use dateadd like crazy.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:Tango@.discussions.microsoft.com:
> > > > > can somebody pls help me with the formula for a datetime parameter
> > > > > that is
> > > > >
> > > > > say 7 days past today. this will be used as the end date parameter.
> > > > >
> > > > > my start_date parameter default is =(today)
> > > > >
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
>
>|||would it be safe to assume that you could insert the starting date parameter
instead of now. so the second date (end date) is x days after the starting
date'
"sathya" wrote:
> Use expression (for seven days before):
> =DateTime.Now.AddDays(-7)
> Use expression (for seven days after):
> =DateTime.Now.AddDays(7)
>
> "Tango" wrote:
> > can somebody pls help me with the formula for a datetime parameter that is
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks|||Hi Ben,
I have just wrote a solution for your previous post.
saglamtimur
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:CA9238B4-5E65-417A-89D8-F70FBD8610A8@.microsoft.com...
> would it be safe to assume that you could insert the starting date
parameter
> instead of now. so the second date (end date) is x days after the starting
> date'
> "sathya" wrote:
> > Use expression (for seven days before):
> > =DateTime.Now.AddDays(-7)
> > Use expression (for seven days after):
> > =DateTime.Now.AddDays(7)
> >
> >
> > "Tango" wrote:
> >
> > > can somebody pls help me with the formula for a datetime parameter
that is
> > > say 7 days past today. this will be used as the end date parameter.
> > >
> > > my start_date parameter default is =(today)
> > >
> > > thanks|||Here's the formula...
=CDate(Parameters!startdate.Value).AddMonths(-1).ToString("MMMM")
"saglamtimur" wrote:
> Hi Ben,
> I have just wrote a solution for your previous post.
> saglamtimur
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:CA9238B4-5E65-417A-89D8-F70FBD8610A8@.microsoft.com...
> > would it be safe to assume that you could insert the starting date
> parameter
> > instead of now. so the second date (end date) is x days after the starting
> > date'
> >
> > "sathya" wrote:
> >
> > > Use expression (for seven days before):
> > > =DateTime.Now.AddDays(-7)
> > > Use expression (for seven days after):
> > > =DateTime.Now.AddDays(7)
> > >
> > >
> > > "Tango" wrote:
> > >
> > > > can somebody pls help me with the formula for a datetime parameter
> that is
> > > > say 7 days past today. this will be used as the end date parameter.
> > > >
> > > > my start_date parameter default is =(today)
> > > >
> > > > thanks
>
>sql
say 7 days past today. this will be used as the end date parameter.
my start_date parameter default is =(today)
thanksTry using SQL to create a dataset.
SELECT dateadd(day,7,getdate()) as end_date
I use dateadd like crazy.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:Tango@.discussions.microsoft.com:
> can somebody pls help me with the formula for a datetime parameter that is
> say 7 days past today. this will be used as the end date parameter.
> my start_date parameter default is =(today)
> thanks|||Thanks John,
I tried adding this statement in the default value of the query & it didnt
work
I also copied your statement into the query string of a new dataset & get
error message 'minimum capacity must be non negative'
Todd
"John Geddes" wrote:
> Try using SQL to create a dataset.
> SELECT dateadd(day,7,getdate()) as end_date
> I use dateadd like crazy.
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:Tango@.discussions.microsoft.com:
> > can somebody pls help me with the formula for a datetime parameter that is
> >
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks
>
>|||undefined function 'getdate' in expression is the error message i get when i
run the query in the new dataset
"John Geddes" wrote:
> Put the statement in a new dataset.
> Then, go to the parameter and select default value from dataset, pick
> your new dataset, and then pick the column name.
> Did that help?
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:Tango@.discussions.microsoft.com:
> > Thanks John,
> >
> > I tried adding this statement in the default value of the query & it didnt
> >
> > work
> > I also copied your statement into the query string of a new dataset & get
> >
> > error message 'minimum capacity must be non negative'
> >
> > Todd
> >
> > "John Geddes" wrote:
> >
> > > Try using SQL to create a dataset.
> > >
> > > SELECT dateadd(day,7,getdate()) as end_date
> > >
> > > I use dateadd like crazy.
> > >
> > >
> > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > news:Tango@.discussions.microsoft.com:
> > > > can somebody pls help me with the formula for a datetime parameter
> > > > that is
> > > >
> > > > say 7 days past today. this will be used as the end date parameter.
> > > >
> > > > my start_date parameter default is =(today)
> > > >
> > > > thanks
> > >
> > >
> > >
>
>|||Thanks saglamtimur
i am now getting an error message 'doesnt have the expected type'
"saglamtimur" wrote:
> If you want vb.net solution I use this function for one week (7 days);
> =format(dateadd("ww",1,Globals!ExecutionTime),"dd/MM/yyyy")
> "ww" equals week, 1 equals 1 week, if you want further info just google
> "vb.net dateadd function"
> Hope helps.
> Regards
> "John Geddes" <john_g@.alamode.com> wrote in message
> news:#KqRU#h4EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Put the statement in a new dataset.
> >
> > Then, go to the parameter and select default value from dataset, pick
> > your new dataset, and then pick the column name.
> >
> > Did that help?
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:Tango@.discussions.microsoft.com:
> > > Thanks John,
> > >
> > > I tried adding this statement in the default value of the query & it
> didnt
> > >
> > > work
> > > I also copied your statement into the query string of a new dataset &
> get
> > >
> > > error message 'minimum capacity must be non negative'
> > >
> > > Todd
> > >
> > > "John Geddes" wrote:
> > >
> > > > Try using SQL to create a dataset.
> > > >
> > > > SELECT dateadd(day,7,getdate()) as end_date
> > > >
> > > > I use dateadd like crazy.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:Tango@.discussions.microsoft.com:
> > > > > can somebody pls help me with the formula for a datetime parameter
> > > > > that is
> > > > >
> > > > > say 7 days past today. this will be used as the end date parameter.
> > > > >
> > > > > my start_date parameter default is =(today)
> > > > >
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
>
>|||Use expression (for seven days before):
=DateTime.Now.AddDays(-7)
Use expression (for seven days after):
=DateTime.Now.AddDays(7)
"Tango" wrote:
> can somebody pls help me with the formula for a datetime parameter that is
> say 7 days past today. this will be used as the end date parameter.
> my start_date parameter default is =(today)
> thanks|||Thank you soooo much sathya
works a charm
"sathya" wrote:
> Use expression (for seven days before):
> =DateTime.Now.AddDays(-7)
> Use expression (for seven days after):
> =DateTime.Now.AddDays(7)
>
> "Tango" wrote:
> > can somebody pls help me with the formula for a datetime parameter that is
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks|||How could this be done w/ a parameter?
=format(dateadd("mm",-1,parameters!stardate),"MMMM")
Thanks...
"saglamtimur" wrote:
> If you want vb.net solution I use this function for one week (7 days);
> =format(dateadd("ww",1,Globals!ExecutionTime),"dd/MM/yyyy")
> "ww" equals week, 1 equals 1 week, if you want further info just google
> "vb.net dateadd function"
> Hope helps.
> Regards
> "John Geddes" <john_g@.alamode.com> wrote in message
> news:#KqRU#h4EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Put the statement in a new dataset.
> >
> > Then, go to the parameter and select default value from dataset, pick
> > your new dataset, and then pick the column name.
> >
> > Did that help?
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:Tango@.discussions.microsoft.com:
> > > Thanks John,
> > >
> > > I tried adding this statement in the default value of the query & it
> didnt
> > >
> > > work
> > > I also copied your statement into the query string of a new dataset &
> get
> > >
> > > error message 'minimum capacity must be non negative'
> > >
> > > Todd
> > >
> > > "John Geddes" wrote:
> > >
> > > > Try using SQL to create a dataset.
> > > >
> > > > SELECT dateadd(day,7,getdate()) as end_date
> > > >
> > > > I use dateadd like crazy.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:Tango@.discussions.microsoft.com:
> > > > > can somebody pls help me with the formula for a datetime parameter
> > > > > that is
> > > > >
> > > > > say 7 days past today. this will be used as the end date parameter.
> > > > >
> > > > > my start_date parameter default is =(today)
> > > > >
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
>
>|||would it be safe to assume that you could insert the starting date parameter
instead of now. so the second date (end date) is x days after the starting
date'
"sathya" wrote:
> Use expression (for seven days before):
> =DateTime.Now.AddDays(-7)
> Use expression (for seven days after):
> =DateTime.Now.AddDays(7)
>
> "Tango" wrote:
> > can somebody pls help me with the formula for a datetime parameter that is
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks|||Hi Ben,
I have just wrote a solution for your previous post.
saglamtimur
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:CA9238B4-5E65-417A-89D8-F70FBD8610A8@.microsoft.com...
> would it be safe to assume that you could insert the starting date
parameter
> instead of now. so the second date (end date) is x days after the starting
> date'
> "sathya" wrote:
> > Use expression (for seven days before):
> > =DateTime.Now.AddDays(-7)
> > Use expression (for seven days after):
> > =DateTime.Now.AddDays(7)
> >
> >
> > "Tango" wrote:
> >
> > > can somebody pls help me with the formula for a datetime parameter
that is
> > > say 7 days past today. this will be used as the end date parameter.
> > >
> > > my start_date parameter default is =(today)
> > >
> > > thanks|||Here's the formula...
=CDate(Parameters!startdate.Value).AddMonths(-1).ToString("MMMM")
"saglamtimur" wrote:
> Hi Ben,
> I have just wrote a solution for your previous post.
> saglamtimur
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:CA9238B4-5E65-417A-89D8-F70FBD8610A8@.microsoft.com...
> > would it be safe to assume that you could insert the starting date
> parameter
> > instead of now. so the second date (end date) is x days after the starting
> > date'
> >
> > "sathya" wrote:
> >
> > > Use expression (for seven days before):
> > > =DateTime.Now.AddDays(-7)
> > > Use expression (for seven days after):
> > > =DateTime.Now.AddDays(7)
> > >
> > >
> > > "Tango" wrote:
> > >
> > > > can somebody pls help me with the formula for a datetime parameter
> that is
> > > > say 7 days past today. this will be used as the end date parameter.
> > > >
> > > > my start_date parameter default is =(today)
> > > >
> > > > thanks
>
>sql
Date Parameter
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
Hi ecn i,
You could write an expression\custom code block to set the parameter default value to be the last day of the current month.
|||Thanks for your reply.
Please give me the example (expression) for finding the last date of the current month
and set it as default.
|||=DateSerial(Year(Now()),Month(Now()) + 2,0)Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||
Thank You Very Much For Your Reply .
It Really help me a lot.
|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||To get the last day of this month, get the first date of next month, then subtract one day from it.
This expression gets the first day of next month:
Code Snippet
=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:
Code Snippet
=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")If you want to avoid a Format statement then you can use the Month and DatePart functions:
Code Snippet
=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.
|||Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")Wednesday, March 21, 2012
Date parameter
My report has a StartDate and a EndDate parameters. I use the datetime
parameter type, and the input textbox shows both the date and time portions.
How to show only the date portion in the parameter textbox? By the way, is it
possible to have something like a datetime picker to get the date parameter?
The textbox control is not user-friendly.When you specify the parameter as date time, both with display although the
user may enter only the date or the time, with the remaing defaulting to the
min...
Some folks will use a string parameter instead...
You could populate the dates from a table in the database... But using a
date picker, you'd have to put an html page in front of the report and
include the date picker in that, then call the report from a web service.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Matthew Chow" <Matthew Chow@.discussions.microsoft.com> wrote in message
news:025C4D51-7562-4532-ACE0-C5C39DF55D3E@.microsoft.com...
> My report has a StartDate and a EndDate parameters. I use the datetime
> parameter type, and the input textbox shows both the date and time
portions.
> How to show only the date portion in the parameter textbox? By the way, is
it
> possible to have something like a datetime picker to get the date
parameter?
> The textbox control is not user-friendly.|||On Mon, 24 Jan 2005 07:47:02 -0800, Matthew Chow <Matthew
Chow@.discussions.microsoft.com> wrote:
>My report has a StartDate and a EndDate parameters. I use the datetime
>parameter type, and the input textbox shows both the date and time portions.
>How to show only the date portion in the parameter textbox? By the way, is it
>possible to have something like a datetime picker to get the date parameter?
>The textbox control is not user-friendly.
DatePicker is rather "too much" to ask :))
As of date parameters, to avoid confusion among end-users with all
that extra time info, just use a string parameter with a prompt like
"Start Date (mm/dd/yyyy):" The proposed format should match the
locale of your server, so even foreign users (if you have any) won't
make mistakes. Then in your query use somethig like this:
WHERE MyTable.MyDate >= CONVERT(datetime, @.pStartDate)
If they enter something invalid, they will get a SQL error, like
"unable to convert to a datetime". This approach is less efficient,
because user's input is being validated on the database level, not on
the web page level, but at the same time provides a much cleaner way
for the users by avoiding confusion. We are now using this everywhere.|||Thanks for helping.sql
parameter type, and the input textbox shows both the date and time portions.
How to show only the date portion in the parameter textbox? By the way, is it
possible to have something like a datetime picker to get the date parameter?
The textbox control is not user-friendly.When you specify the parameter as date time, both with display although the
user may enter only the date or the time, with the remaing defaulting to the
min...
Some folks will use a string parameter instead...
You could populate the dates from a table in the database... But using a
date picker, you'd have to put an html page in front of the report and
include the date picker in that, then call the report from a web service.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Matthew Chow" <Matthew Chow@.discussions.microsoft.com> wrote in message
news:025C4D51-7562-4532-ACE0-C5C39DF55D3E@.microsoft.com...
> My report has a StartDate and a EndDate parameters. I use the datetime
> parameter type, and the input textbox shows both the date and time
portions.
> How to show only the date portion in the parameter textbox? By the way, is
it
> possible to have something like a datetime picker to get the date
parameter?
> The textbox control is not user-friendly.|||On Mon, 24 Jan 2005 07:47:02 -0800, Matthew Chow <Matthew
Chow@.discussions.microsoft.com> wrote:
>My report has a StartDate and a EndDate parameters. I use the datetime
>parameter type, and the input textbox shows both the date and time portions.
>How to show only the date portion in the parameter textbox? By the way, is it
>possible to have something like a datetime picker to get the date parameter?
>The textbox control is not user-friendly.
DatePicker is rather "too much" to ask :))
As of date parameters, to avoid confusion among end-users with all
that extra time info, just use a string parameter with a prompt like
"Start Date (mm/dd/yyyy):" The proposed format should match the
locale of your server, so even foreign users (if you have any) won't
make mistakes. Then in your query use somethig like this:
WHERE MyTable.MyDate >= CONVERT(datetime, @.pStartDate)
If they enter something invalid, they will get a SQL error, like
"unable to convert to a datetime". This approach is less efficient,
because user's input is being validated on the database level, not on
the web page level, but at the same time provides a much cleaner way
for the users by avoiding confusion. We are now using this everywhere.|||Thanks for helping.sql
Date Parameter
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
Hi ecn i,
You could write an expression\custom code block to set the parameter default value to be the last day of the current month.
|||Thanks for your reply.
Please give me the example (expression) for finding the last date of the current month
and set it as default.
|||=DateSerial(Year(Now()),Month(Now()) + 2,0)Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||
Thank You Very Much For Your Reply .
It Really help me a lot.
|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||To get the last day of this month, get the first date of next month, then subtract one day from it.
This expression gets the first day of next month:
Code Snippet
=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:
Code Snippet
=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")If you want to avoid a Format statement then you can use the Month and DatePart functions:
Code Snippet
=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.
|||Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")Date Parameter
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
Hi ecn i,
You could write an expression\custom code block to set the parameter default value to be the last day of the current month.
|||Thanks for your reply.
Please give me the example (expression) for finding the last date of the current month
and set it as default.
|||=DateSerial(Year(Now()),Month(Now()) + 2,0)Just change the 2 to 1 to return the end date of the current month.
The report parameter was set to datetime when I used this.
|||
Thank You Very Much For Your Reply .
It Really help me a lot.
|||Hi...What is the best way to get the month and date integers for the last date of the current month?|||To get the last day of this month, get the first date of next month, then subtract one day from it.
This expression gets the first day of next month:
Code Snippet
=CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")Note that i create the date string in ISO format so that there is no confusion between days and months. Next we subtract a day:
Code Snippet
=DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1"))This gives us a date representing the last day of this month. Then you can just surround that with a Format statement to output it as a day and month (just change the formatting string if you want month/day ordering or a different delimiter):
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "dd/MM")If you want to avoid a Format statement then you can use the Month and DatePart functions:
Code Snippet
=Month(Now) & "/" & DatePart("d", DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")))Of course, where i have done a CDate() on an assembled string, you can just use DateSerial(year, month, day) to achieve the same thing - i just used a string to make what was happening even more obvious.
|||Thanks! This is just what I needed. I needed month then date with leading zeros...like 0531 for May 31 without the slash. I just tweaked the "dd/MM" to "MMdd" and all is good.
Code Snippet
=Format(DateAdd("d", -1, CDate(Year(DateAdd("M", 1, Now)) & "/" & Month(DateAdd("M", 1, Now)) & "/" & "1")), "MMdd")
Subscribe to:
Posts (Atom)