Showing posts with label table. Show all posts
Showing posts with label table. Show all posts

Thursday, March 29, 2012

Date Range Problem

I have a report with one table that has a start-date field that I would
like to use to filter the results on the report. I do not want the
user to enter a date instead I would like to create a parameter in a
drop down that they can choose Period1, Period 2 and so on.
There is not a Period column to reference to and I am not sure how to
use a start and end date to reference different parameters. Is there a
way to use the start_date and statically assign a date range value to a
parameter and then have the results filtered back based on the
parameter?
Basically here is what I am trying to do...
Parameter Value
Period 1: 01/02/06 through 02/05/06 (these values come
from the start-date field
Period 2: 02/06/06 through 03/05/06
Period 3: 03/06/06 through 04/05/06
and so on for twelve periods.
I know how to create a non-queried parameter but I don't know how to
set the value to reference a date range.
Any help is greatly appreciated!Is your date range is fixed.
ie Period 2: 02/06/06 through 03/05/06 is this date is fixed
meaning for period 2 always you will get 02/06/06 through 03/05/06 then it
can be done.
try this code in your data tab.
if @.period = 1
select * from ABC where [start_date] between '2005/1/01' and '2005/1/31'
else
select * from ABC where [start_date] between '2005/2/01' and '2005/2/31'
and so on....
when you select the period dependiong on the period selected it executes the
query
Amarnath
"swtjen01" wrote:
> I have a report with one table that has a start-date field that I would
> like to use to filter the results on the report. I do not want the
> user to enter a date instead I would like to create a parameter in a
> drop down that they can choose Period1, Period 2 and so on.
> There is not a Period column to reference to and I am not sure how to
> use a start and end date to reference different parameters. Is there a
> way to use the start_date and statically assign a date range value to a
> parameter and then have the results filtered back based on the
> parameter?
> Basically here is what I am trying to do...
> Parameter Value
> Period 1: 01/02/06 through 02/05/06 (these values come
> from the start-date field
> Period 2: 02/06/06 through 03/05/06
> Period 3: 03/06/06 through 04/05/06
> and so on for twelve periods.
> I know how to create a non-queried parameter but I don't know how to
> set the value to reference a date range.
> Any help is greatly appreciated!
>sql

Date Range logic

Good morning all,

I've got a little headscratcher for you involving date ranges.

We have a table for recording absences:
Absence(unique_identifier, parent_identifier, date_from, date_to ... )
And an employee table
Employees(unique_identifier, Surname, Firstname, birth_date ...)

Where the relationship between the two is:
Employees.unique_identifier = Absence.parent_identifier

The problem lies when wanting to know whether an employee was off within a specified date range.

Absence:

u_id p_id date_from date_to
1 1 2007-02-01 2007-02-06
2 2 2007-01-29 2007-02-06
3 2 2007-03-25 2007-03-25
4 3 2007-06-06 2007-06-08
5 4 2007-02-05 2007-02-06

Given the above sample results, how can I identify which employees were off during the first week of February (2007-02-01 to 2007-02-07)?

Expected Results:

u_id p_id date_from date_to
1 1 2007-02-01 2007-02-06
2 2 2007-01-29 2007-02-06
5 4 2007-02-05 2007-02-06

Any advice you can give to help me get the answer I need is much appreciated :)Why's the middle line in red? I don't know the SQL for that...|||Errr... There was a reason for that, which I now realise is meaningless because I didn't post the SQL I tried already...
EDIT: I say "I", I actually mean "an employee using the WYSIWYG query editor built into a system.

SELECT p_id FROM Absence WHERE date_from BETWEEN '20070201' AND '20070207'

Clearly won't return that line, which needs including.

Did I mention that I want the results in bold, aligned right and every other line needs to be red? I am using Query Analyzer.|||I don't get the difficulty. Am I missing something?


SELECT p_id
FROM Absence
WHERE date_from BETWEEN '20070201' AND '20070207'
OR date_to BETWEEN '20070201' AND '20070207'|||Unless he wants employees that are on leave for the FULL period of
Feb 1 to Feb 6 ?|||Poots, would the following row be returned?

u_id p_id date_from date_to
6 7 2007-01-25 2007-03-01

The above was off during the period in question|||SELECT p_id
FROM Absence
WHERE date_from <= '20070207'
AND date_to >= '20070201'|||Poots, would the following row be returned?No - and it illustrates the error in concentrating on inadequate sample data instead of the logic of the problem :p|||Thanks for the solution Peso and thanks for your help Poots!
I can't believe how long I've been staring at this and not been able to get my head round a logical answer!

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
----
--
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
----
--
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> ----
--
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> ----
--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?

> Please let me know how to get this record also.
> Thanks in advance
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate =
=20
> ToDate
> ----=
--=AD--
> 1 10.00 01/01/2005 =
=20
> 12/31/2005
> 1 9.00 01/01/2004 =
=20
> 12/31/2004
> 2 2.00 01/01/1900 =
=20
> 01/01/9999
> ----=
--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =
get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=
ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivassql

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?

> Please let me know how to get this record also.
> Thanks in advance
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> ----X--
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> ----X--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >= 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >= 01/01/1900 and ToDate <= '01/01/9999' )
Note: < 01/01/2006 instead of <= 12/31/2005' to take care of time
portion
M A Srinivas

DATE RANGE ISSUE

Hi All,
I have below table structure:
ItemID Price FromDate
ToDate
-----
1 10.00 01/01/2005
12/31/2005
1 9.00 01/01/2004
12/31/2004
2 2.00 01/01/1900
01/01/9999
-----
Now I need to write a query to get price for all the items for the date
range 01/01/2005 to 12/31/2005
If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
ie, daterange specified lies between 1900 and 9999. But the query will not
give that result.
Please let me know how to get this record also.
Thanks in advance"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:628A35F2-9F1E-4E5A-B095-E5A45470CC84@.microsoft.com...
> Hi All,
> I have below table structure:
> ItemID Price FromDate
> ToDate
> -----
> 1 10.00 01/01/2005
> 12/31/2005
> 1 9.00 01/01/2004
> 12/31/2004
> 2 2.00 01/01/1900
> 01/01/9999
> -----
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
>
> If I use FromDate >= 01/01/2005 and ToDate <= 01/01/2005 then I will get
> only one record (ItemID=1). But ItemID =2 also lies in the same daterange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
>
The only records your query should return are ones with a date of
01/01/2005. I'm not sure how it's returning ItemID 1
ItemID=2 does not lay within that same daterange.
It's fromdate 01/01/1900 is less than 01/01/2005 and it's Todate, is greater
than 01/01/2005.
With the AND, you're asking for stuff BETWEEN 01/01/2005 and 01/01/2005.
ItemID=2 exceeds this range on both ends.
I suspect you want OR?
> Please let me know how to get this record also.
> Thanks in advance
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 14, 9:49 am, Ram <R...@.discussions.microsoft.com> wrote:
> Hi All,
> I have below table structure:
> ItemID Price FromDate = > ToDate
> ----=--=AD--
> 1 10.00 01/01/2005 = > 12/31/2005
> 1 9.00 01/01/2004 = > 12/31/2004
> 2 2.00 01/01/1900 = > 01/01/9999
> ----=--=AD--
> Now I need to write a query to get price for all the items for the date
> range 01/01/2005 to 12/31/2005
> If I use FromDate >=3D 01/01/2005 and ToDate <=3D 01/01/2005 then I will =get
> only one record (ItemID=3D1). But ItemID =3D2 also lies in the same dater=ange,
> ie, daterange specified lies between 1900 and 9999. But the query will not
> give that result.
> Please let me know how to get this record also.
> Thanks in advance
I think you are storing dummy values in fromdate and todate column
when it is unknown (NULL)
If that is the case
(FromDate >=3D 01/01/2005 and ToDate < '01/01/2006' )
OR
FromDate >=3D 01/01/1900 and ToDate <=3D '01/01/9999' )
Note: < 01/01/2006 instead of <=3D 12/31/2005' to take care of time
portion
M A Srinivas

Date range + parallel time

Hi,

I'm quite new to MDX and found a problem I can not solve. I have a fact table which contains a start date and an end date, plus several measures. I also have a time dimension, with years and months. I need to dynamically build an mdx query that, given a year and a month, would show any measure in the fact table whose start date is lower than the given date and the end date is higher than the given date. To complicate things a bit, the same query must show the same measure in the previous year to the given date. Both results must be in the same axis.

We know how to show each result separately, using date ranges in the WHERE clause, but have no idea about how to combine both results.

Thanks

--eduardj

Finally solved it.

I created a date range dimension, with all the date ranges in the original fact table, and a measure-less fact table with a relationship with the new date range dimension and the old time dimension. After that, I created a Many-to-Many relationship between the original fact table and the time dimension through the newly created fact table. Besides, I created a hierarchy in the time dimension which related the times (parallel) that always had to be shown together.

Kind of messy, but it works beautifully.

thanks

--eduardj

Date Range

Hi Everybody,
I need to return a values in a specific format
I have a Table which has Start_date and End_Date as
Start_Date End_date
'1/28/2006' and '1/31/2006'
I want to return values as
Start_date End_Date
1/28/2006 1/29/2006
1/29/2006 1/30/2006
1/30/2006 1/31/2006
How do I do this? I dont care If it is a function or a sp or a Select
statement.
Any help is sincerely appreciated.
TIA,
SunnyYou want to use ISO-8601 formats so your code will port. Next,
formatting in done in the front end and not in the database.|||Sunny
Inserting dates into SQL Server you'll have to use YYYYMMDD format.
If yopu want to display dates on the client , use FORMAT(VB6) for instance.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||If I am understanding what you want correctly, you want a row for each day
between the start and end dates in your table.
For this you can use a calendar table as the example below shows:
CREATE TABLE Calendar
(
DateValue DateTime
)
GO
CREATE TABLE MyDates
(
ID INT NOT NULL PRIMARY KEY,
StartDate DateTime,
EndDate DateTime
)
GO
-- FIll The calendar Table
DECLARE @.TempDate DateTime
SET @.TempDate = '2005-11-01'
WHILE @.TempDate <= '2006-06-30'
BEGIN
INSERT INTO Calendar(DateValue)
VALUES (@.TempDate)
SET @.TempDate = DATEADD(d,1, @.TempDate)
END
-- Put data in MyDates
INSERT INTO MyDates (ID, StartDate, EndDate)
SELECT 1,'2005-12-28','2005-12-31'
UNION
SELECT 2,'2005-12-28','2006-01-05'
-- Now select the date ranges for a single row
SELECT DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID = 1
-- Now demonstrate that it works using multiple rows
SELECT MyDates.ID AS ID,
DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID IN (1,2)
DROP TABLE Calendar
GO
DROP TABLE MyDates
GO
I do agree with the other posts that you should use the ISO date standard
(as shown in my example) and then format the dates on the client to the
user's desired display format.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||Hi Sunny,
You might want to use DD MMM YYYY format as in the UK there are not 28
months in a year and we use the format DD/MM/YYYY, alternatively use
YYYYMMDD.
Anyway, this SQL using a sequence table will do what you require...
declare @.seqtable table (
seqnumber tinyint not null
)
insert @.seqtable ( seqnumber ) values ( 1 )
insert @.seqtable ( seqnumber ) values ( 2 )
insert @.seqtable ( seqnumber ) values ( 3 )
insert @.seqtable ( seqnumber ) values ( 4 )
insert @.seqtable ( seqnumber ) values ( 5 )
insert @.seqtable ( seqnumber ) values ( 6 )
insert @.seqtable ( seqnumber ) values ( 7 )
insert @.seqtable ( seqnumber ) values ( 8 )
select start_date = dateadd( day, seqnumber, '28 jan 2006' ),
end_date = dateadd( day, seqnumber+1, '28 jan 2006' )
from @.seqtable
order by start_date
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||What formatting?
The poster wants to expand a date range into a range of rows, 1 day per row.
Are we now not to do these simplest of things in the RDBMS to keep things
simple sweet?
Why on earth would you want to go to the efforts of doing this in a 3gl when
its such a simple task using SQL?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138671987.369279.135430@.g49g2000cwa.googlegroups.com...
> You want to use ISO-8601 formats so your code will port. Next,
> formatting in done in the front end and not in the database.
>|||Sunny,
will it help you?
SELECT DATEADD(D, S.seq - 1, P.beg_date) AS today,
DATEADD(D, S.seq, P.beg_date) AS tomorrow
FROM (SELECT hundred * 100 + ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Hundreds(hundred)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit)) AS S(seq),
(SELECT CAST('20060128' AS DATETIME),
CAST('20060131' AS DATETIME)
UNION ALL
SELECT CAST('20060225' AS DATETIME),
CAST('20060303' AS DATETIME)
UNION ALL
SELECT CAST('20051225' AS DATETIME),
CAST('20060103' AS DATETIME)) AS P(beg_date, fin_date)
WHERE S.seq <= DATEDIFF(D, P.beg_date, P.fin_date);
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

Date Range

------------------------

sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & "where date between @.from_date and @.to_date", conn)

sqladapter.SelectCommand.Parameters.Add("@.from_dat e", OdbcType.date, "date").Value = Me.DateTimePicker1.Value.ToShortDateString

sqladapter.SelectCommand.Parameters.Add("@.to_date" , OdbcType.date, "date").Value = Me.DateTimePicker2.Value.ToShortDateString

i tried these codes but when was prompt error " date type cannot convert to int"

how do i solve the prob? any idea?

Quote:

Originally Posted by girl

------------------------

sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & "where date between @.from_date and @.to_date", conn)

sqladapter.SelectCommand.Parameters.Add("@.from_dat e", OdbcType.date, "date").Value = Me.DateTimePicker1.Value.ToShortDateString

sqladapter.SelectCommand.Parameters.Add("@.to_date" , OdbcType.date, "date").Value = Me.DateTimePicker2.Value.ToShortDateString

i tried these codes but when was prompt error " date type cannot convert to int"

how do i solve the prob? any idea?


Add single quote

' "& table & " '

sqladapter.SelectCommand = New OdbcCommand("SELECT * from '" & table & "' where date between @.from_date and @.to_date", conn)|||sqladapter.SelectCommand = New OdbcCommand("SELECT * from '" & table & "'where date between @.from_date and @.to_date", conn)

sqladapter.SelectCommand.Parameters.Add("@.from_date", OdbcType.Date).Value = Me.MonthCalendar1.SelectionStart.Date

sqladapter.SelectCommand.Parameters.Add("@.to_date", OdbcType.Date).Value = Me.MonthCalendar2.SelectionEnd.Date

sqladapter.Fill(ds, "" & table & "")

i did what you say but it prompt me @. the .fill statement
Optional feature not included!! what should i do??

Tuesday, March 27, 2012

Date query question!

Hi,

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

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

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

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

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

Simonsql

date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

Date query problem!

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

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

Date Query 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),
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:
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

Date query help

I have a table with a date_created and date_expired and I want to look
at the 15th of each month and every month since the system started and
count up how many accounts were active at each of those points in
time.
Will have to assume that if there's an expiry date bigger than today
then that account was active at each of those points in time.

Can someone pelase tell me how can I do that?

Thanks a lotOn 19 Mar 2007 10:14:33 -0700, Giorgio wrote:

Quote:

Originally Posted by

>I have a table with a date_created and date_expired and I want to look
>at the 15th of each month and every month since the system started and
>count up how many accounts were active at each of those points in
>time.
>Will have to assume that if there's an expiry date bigger than today
>then that account was active at each of those points in time.
>
>Can someone pelase tell me how can I do that?
>
>Thanks a lot


Hi Giorgio,

This question has already been answered in
microsoft.public.sqlserver.programming. In the future, please do not
multipost your questions, as it might cause people to waste time
figuring out an answer to a question that's already been answered
elsewhere.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

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

Date Query

how would i query the table with dateid for a particular date
DateID
2005-11-24 14:40:00
select * from tbl1 where dateid like '2005-11-24%' ?
how about date and time like 9-11am of somethingUmmm.. How about

select * from tbl1 where dateid >= '2005-11-24 09:00:00' and dateid < '2005-11-24 12:00:00'|||For the date only, you can:
SELECT * FROM tbl1 WHERE CONVERT(varchar(10), DateID, 101) = '2005-11-24'

For the date/time range, you would just use BETWEEN, as in:
SELECT * FROM tbl1 WHERE DateID BETWEEN '2005-11-24 09:00:00' AND '2005-11-24 11:00:00' (or use bmalar's suggestion above).|||I'd like to see just how that BETWEEN works. I'm thinking at at the very least you'll need to decorate the DateID a bit.

-PatP|||I dunno 'bout all that... ;)

I didn't have anything to get too fancy with testing, but thisSELECT * FROM currentportfolio
WHERE createdate
BETWEEN '2005-11-24 23:59:59' AND '2005-11-25 00:00:01'
managed to find all the rows in my DB with a smalldatetime that was '2005-11-25 00:00:00' (actually inserted by doing the ole' faithful CONVERT(varchar(10), GETDATE(), 101)

and this:
SELECT *
FROM eventlog
WHERE date BETWEEN '2005-11-25 13:55:02' AND '2005-11-25 13:55:03'
order by date

managed to find everything in my table with a datetime within the two seconds indicated (9 rows)

whatchew talkin' 'bout, Willis? What means "decorate"?|||I must have been doing drugs...

The thought that was running through my head was that you were mixing BETWEEN and LIKE in the same expression... I wanted to see just how that would work!

-PatP|||do u mean just specify date without time or time without date? i usually use datediff and datepart

Nov 24, 2005
select * from tbl1 where datediff(day,dateid,'20051124')=0

09:00 - 11.00
select * from tbl1 where (datepart(hour,dateid) between 9 and 10) or (datepart(hour,dateid)=11 and datepart(minute,dateid)=0)|||The thought that was running through my head was that you were mixing BETWEEN and LIKE in the same expression... That's how the today's kids write SQL:
Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.|||That's how the today's kids write SQL:
Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.

ROTFLMAO :D

Like, um, okay dude...|||That's how the today's kids write SQL:
Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.
What scares me about that is I read it and it sounded like perfect English to me ;) But I blame my two high-school aged daughters for that...the older boys, thank goodness, graduated without ever uttering the word "like" in a sentence in that way...

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

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
>
>

Date problems between VB and SQL

I have a problem that I have spent an age on and can't resolve.

Basically, I have a table that has a date field. I have created SP's to
return values within a given date range.

The problem that I have is that when I append some data to the table,
inconsistency occurs.

What basically happens is that when I run the SP within SQL Analyser,
the resultset returns correctly, including ALL data between the two date
ranges. I can see the query in SQL Profiler and can see that the correct
SP and parameters are being passed to SQL.

The problem occurs when I run the SP from VB. If I pass the date
parameters from the original table (before I append any data), the
correct set of results are returned. However, if I then APPEND some
data, VB returns a "Runtime Error 3265 - this item cannot be found in
the collection corresponding to the requested name or ordinal."

In other words, if the original table contains data from say the 1st to
the 8th February, the SP's work fine. If I then add data for the 9th, VB
returns the error indicated.

What is really strange is that at all times, the correct SP and
parameters are being passed to SQL (I can see this using SQL Profiler)
and if I cut and paste the SP and parameters into SQL Analyser, the
correct data is always returned, even if the date range includes the
appended data.

The table is set up correctly using the correct data type for the
column.

I am thinking that this has something to do with the appended data - it
all appends OK so I assume that SQL is happy with the data and data
types. Would it have something to do with the SP being created on the
original table and not on the latest table that has had data appended to
it?

I'll also post this in the VB section as I'm not really sure where this
item should be posted.

Very many thanks for your kind consideration.

Shane

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Shane (Shane) writes:
> The problem occurs when I run the SP from VB. If I pass the date
> parameters from the original table (before I append any data), the
> correct set of results are returned. However, if I then APPEND some
> data, VB returns a "Runtime Error 3265 - this item cannot be found in
> the collection corresponding to the requested name or ordinal."
> In other words, if the original table contains data from say the 1st to
> the 8th February, the SP's work fine. If I then add data for the 9th, VB
> returns the error indicated.
> What is really strange is that at all times, the correct SP and
> parameters are being passed to SQL (I can see this using SQL Profiler)
> and if I cut and paste the SP and parameters into SQL Analyser, the
> correct data is always returned, even if the date range includes the
> appended data.

A lot of text, but little code...

I don't even understand when you get the VB Error. Is when you use .Append?
Or is it when you call the procedure after you have appended?

And with which collection do you get the error?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Date Problem!

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

Date problem in SQL Server

Hi

Good day!

I am having some problem with the date-time datatype. I want to store only date in my table in this format - DD-MM-YYYY - and I do not need time. This must be compatible with the format of Visual Basic.

How to achieve this?

SQL Server datetime datatype stores both DATE and TIME. [datetime] has a presicion to milliseconds, and [shortdatetime] has a precision to minutes.

If you do NOT provide the TIME component, it will default to midnight (or 12:00:00 AM). It is very common for applications to ignore the time component. If you do not need the TIME component, use a shortdatetime datatype.

Refer to Books Online about [datetime] and [shortdatetime] datatypes.

|||

what is the problem in storing date and time. is it because of Storage size or is it because u wnat to show only date to the user. If you want to show only date.... this formating can be done at the frontend. Don't change the datatype... you store the data as datetime only and do the formating at the frontend. And also check CONVERT/CAST function in BOL.

Madhu

|||

You cannot do this with SQL Server 2005 and previous release. In Katmai (next release), we define several new datetime data types and you will be able to do it.

Or you can use the techniques mentioned by the previous posts.

Thanks.

|||

Hi Madhu

My problem is actually in queiry. When I use the type date/time and I assign a default value to GETDATE() in sql server, it stores both date and time. But when I run sproc with where clause like - WHERE Date = @.Date - where the time is not given, I can't find the date!

How to solve it? I am using VB to run the sproc.

Regards

Kapalic

|||

in this case you may need to use CONVERT Function of SQL Server. Suppose u r passing date mm/dd/yyyy then

Where convert(varchar(10),Date,101)='04-10-2007'

Madhu

|||

That is because when you are using @.Date without a time portion, it defaults to midnight (12:00:00.000), and you don't have any rows with that exact time.

Try the following WHERE clause:

Code Snippet


WHERE ( [Date] >= @.Date

AND [Date] < ( dateadd( day, 1, @.Date )

)


This assumes that @.Date is in the form of 'mm/dd/yyyy', and that you want all values from midnight until the last possible moment before the next midnight (Or [ >= 12:00:00.000 ] AND [ < 11:59:59.997 ]

(You don't really want to follow Madhu's suggestion of using convert(varchar(10), Date, 101) BECAUSE it would invalidate the use of an index and could be very inefficient.)

|||

Hi Arnie,

Thnk you very much for your reply. The problem is, I want to use the date format as DD/MM/YYYY. This I forgot to mention. And I want to link the sproc with a DateTimePicker control in Visual Basic.

Please resque...

Regards

Kapalic

|||

Read about CONVERT Function in BOL ... what u need to do is ... convert the date in sql to which ever format u required. the below statement will do that

if the supplied date is 04/10/2007 dd/mm/yyyy run the below mentioned statement and see teh result

Where convert(varchar(10),Date,105)='04-10-2007'

Madhu

|||

Using a VB date format of 'DD/MM/YYYY', and using a DateTimePicker is NOT a problem as long as you are using a Date datatype in VB, and the procedure call is using a SQL Datetime datatype.

If your VB date variable it used when you make the SQL Stored procedure call, the WHERE clause example above will work just fine.