DateTime

I have a datetime field and would like to get a new field (in a view) that s
ubstracts 10mn from it. Any function out there to do that?
ThanksA quick example:
CREATE TABLE Tx (i datetime)
INSERT INTO Tx (i) SELECT '20040512 16:50'
GO
CREATE VIEW Vx AS SELECT i, DATEADD(mi, -10, i) AS NewColumn FROM Tx
GO
SELECT * FROM Vx
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:157DB94F-A674-4A2F-A2A1-0704B278C5BB@.microsoft.com...
I have a datetime field and would like to get a new field (in a view) that
substracts 10mn from it. Any function out there to do that?
Thanks

DateTime

Sql does not just have a "Date" datatype? I see SmallDateTime and
DateTime, but what if I only want date?Not in the current version. This is planned for the next version (Yukon).
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns947A7A6746E9JimHeaveyhotmailcom@.
207.46.248.16...

quote:

---

> Sql does not just have a "Date" datatype? I see SmallDateTime and
> DateTime, but what if I only want date?

---

|||In addition to Dan's response, see
http://www.aspfaq.com/2206
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns947A7A6746E9JimHeaveyhotmailcom@.
207.46.248.16...

quote:

---

> Sql does not just have a "Date" datatype? I see SmallDateTime and
> DateTime, but what if I only want date?

---

|||Key word is *planned*.
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23jHqAZq4DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Not in the current version. This is planned for the next version (Yukon).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jim Heavey" <JimHeavey@.nospam.com> wrote in message
> news:Xns947A7A6746E9JimHeaveyhotmailcom@.
207.46.248.16...
>
>

---

DateTime

I have to check whether a given date is between a day and a month.
A guided tour is only scheduled from november 1st until april 1st.
when i want to make a reservation for the tour Today the query should
tell me that today is out of range.

In the database we store this as seperate fields:
from day integer 1 to 7
from month integer 1 to 12

till day integer 1 to 7
till month integer 1 to 12

when Today is between november 1st and march 30 then NoGood
when Today is not between november 1st and march 30 then Proceed

I have tried a number of variations but without any results yet.
I hope someone can give a tip on how to solve this particular case
Many thanks in advance
eddyHi

You could try something like:

DECLARE @.baseDate datetime
SET @.baseDate = '20021231'

SELECT *
FROM Tours
WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day, [From
Day], @.baseDate ) )
AND dateadd(Month, [To Month], dateadd ( day, [To Day], @.baseDate ) )

John

"Eddy" <eddy@.atwork4u.be> wrote in message
news:3f819965$0$25656$ba620e4c@.reader0.news.skynet .be...
> I have to check whether a given date is between a day and a month.
> A guided tour is only scheduled from november 1st until april 1st.
> when i want to make a reservation for the tour Today the query should
> tell me that today is out of range.
> In the database we store this as seperate fields:
> from day integer 1 to 7
> from month integer 1 to 12
> till day integer 1 to 7
> till month integer 1 to 12
> when Today is between november 1st and march 30 then NoGood
> when Today is not between november 1st and march 30 then Proceed
> I have tried a number of variations but without any results yet.
> I hope someone can give a tip on how to solve this particular case
> Many thanks in advance
> eddy|||> In the database we store this as seperate fields:
> from day integer 1 to 7
> from month integer 1 to 12

Why? Not a very effective way to store dates when you have a proper DATETIME
datatype to do the job. Change your columns to DATETIME.

If you want to ignore the year then just put a dummy year number on your
dates:

CREATE TABLE Sometable (fromdate DATETIME NOT NULL, todate DATETIME, PRIMARY
KEY (fromdate), CHECK (fromdate<todate))
INSERT INTO Sometable VALUES ('19001101','19010401')

Then do the query like this:

DECLARE @.some_date DATETIME
SET @.some_date = CURRENT_TIMESTAMP

SELECT *
FROM Sometable
WHERE DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date),@.some_date) BETWEEN
fromdate AND todate
OR DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date)+1,@.some_date) BETWEEN
fromdate AND todate

Of course, if the year is relevant you can just do:

SELECT *
FROM Sometable
WHERE @.some_date BETWEEN fromdate AND todate

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks for the tip...
I have considered datetime fields a while ago...
It is more or less working adding a dummy year into the equation...
but i'll reconsider.
Having a year means the users will have to add a record for the coming
years. But it could prove to be more flexible this way.
Anyway thanks for the help...

David Portas wrote:

>>In the database we store this as seperate fields:
>>from day integer 1 to 7
>>from month integer 1 to 12
>
> Why? Not a very effective way to store dates when you have a proper DATETIME
> datatype to do the job. Change your columns to DATETIME.
> If you want to ignore the year then just put a dummy year number on your
> dates:
> CREATE TABLE Sometable (fromdate DATETIME NOT NULL, todate DATETIME, PRIMARY
> KEY (fromdate), CHECK (fromdate<todate))
> INSERT INTO Sometable VALUES ('19001101','19010401')
> Then do the query like this:
> DECLARE @.some_date DATETIME
> SET @.some_date = CURRENT_TIMESTAMP
> SELECT *
> FROM Sometable
> WHERE DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date),@.some_date) BETWEEN
> fromdate AND todate
> OR DATEADD(YEAR,YEAR(fromdate)-YEAR(@.some_date)+1,@.some_date) BETWEEN
> fromdate AND todate
> Of course, if the year is relevant you can just do:
> SELECT *
> FROM Sometable
> WHERE @.some_date BETWEEN fromdate AND todate|||Hi,
thanks for the reply.
need to think this through though ... what date is @.BaseDate

getdate() could be about any date the user choses

thanks
eddy galle

John Bell wrote:

> Hi
> You could try something like:
> DECLARE @.baseDate datetime
> SET @.baseDate = '20021231'
> SELECT *
> FROM Tours
> WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day, [From
> Day], @.baseDate ) )
> AND dateadd(Month, [To Month], dateadd ( day, [To Day], @.baseDate ) )
> John
> "Eddy" <eddy@.atwork4u.be> wrote in message
> news:3f819965$0$25656$ba620e4c@.reader0.news.skynet .be...
>>I have to check whether a given date is between a day and a month.
>>A guided tour is only scheduled from november 1st until april 1st.
>>when i want to make a reservation for the tour Today the query should
>>tell me that today is out of range.
>>
>>In the database we store this as seperate fields:
>>from day integer 1 to 7
>>from month integer 1 to 12
>>
>>till day integer 1 to 7
>>till month integer 1 to 12
>>
>>when Today is between november 1st and march 30 then NoGood
>>when Today is not between november 1st and march 30 then Proceed
>>
>>I have tried a number of variations but without any results yet.
>>I hope someone can give a tip on how to solve this particular case
>>Many thanks in advance
>>eddy
>>|||> Having a year means the users will have to add a record for the coming
> years. But it could prove to be more flexible this way.

Have another look at my example. You don't have to do this - the query works
without it.

--
David Portas
----
Please reply only to the newsgroup
--|||In article <16mdnUmGHswBIRyiRVn-vg@.giganews.com>,
REMOVE_BEFORE_REPLYING_dportas@.acm.org says...
> > Having a year means the users will have to add a record for the coming
> > years. But it could prove to be more flexible this way.
> Have another look at my example. You don't have to do this - the query works
> without it.

I would think that it would be important to have a year because
depending on the year, the difference between two identical dates can
differ.

-- Rick|||Hi

Basedate is the date that you used to get the offset for your month and year
columns.

John

"Eddy" <eddy@.atwork4u.be> wrote in message
news:3f81a7da$0$24180$ba620e4c@.reader0.news.skynet .be...
> Hi,
> thanks for the reply.
> need to think this through though ... what date is @.BaseDate
> getdate() could be about any date the user choses
> thanks
> eddy galle
> John Bell wrote:
> > Hi
> > You could try something like:
> > DECLARE @.baseDate datetime
> > SET @.baseDate = '20021231'
> > SELECT *
> > FROM Tours
> > WHERE getdate() BETWEEN dateadd(Month, [From Month], dateadd ( day,
[From
> > Day], @.baseDate ) )
> > AND dateadd(Month, [To Month], dateadd ( day, [To Day], @.baseDate ) )
> > John
> > "Eddy" <eddy@.atwork4u.be> wrote in message
> > news:3f819965$0$25656$ba620e4c@.reader0.news.skynet .be...
> >>I have to check whether a given date is between a day and a month.
> >>A guided tour is only scheduled from november 1st until april 1st.
> >>when i want to make a reservation for the tour Today the query should
> >>tell me that today is out of range.
> >>
> >>In the database we store this as seperate fields:
> >>from day integer 1 to 7
> >>from month integer 1 to 12
> >>
> >>till day integer 1 to 7
> >>till month integer 1 to 12
> >>
> >>when Today is between november 1st and march 30 then NoGood
> >>when Today is not between november 1st and march 30 then Proceed
> >>
> >>I have tried a number of variations but without any results yet.
> >>I hope someone can give a tip on how to solve this particular case
> >>Many thanks in advance
> >>eddy
> >

DateTime

Hello,

Please can you point me in the right direction.

I have a table (JobMain) that holds a list of all jobs that out
engineers need to work on or have worked on.

I would like to select jobs that fall between Monday - Friday 8am to
7pm.

The tables holds a load of colums ... the two that are of interest to
me are

JobId (Primary Key) - Job Number
StartDate (Datetime datatype) - Job start date and time

regards

RobOn Jun 19, 4:21 pm, roblowein <rob.low...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
Please can you point me in the right direction.
>
I have a table (JobMain) that holds a list of all jobs that out
engineers need to work on or have worked on.
>
I would like to select jobs that fall between Monday - Friday 8am to
7pm.
>
The tables holds a load of colums ... the two that are of interest to
me are
>
JobId (Primary Key) - Job Number
StartDate (Datetime datatype) - Job start date and time
>
regards
>
Rob

DECLARE @.startdate datetime ,@.enddate datetime

SET @.startdate = '2007-06-18 08:00:00' -- Monday 8 AM
SET @.enddate = DATEADD(DD,4 ,DATEADD(HOUR,11,@.startdate)) --

SELECT @.startdate,@.enddate

SELECT * FROM yourtable
WHERE StartDate BETWEEN @.startdate AND enddate|||Hello, Rob

Try something like this:

SELECT * FROM YourTable
WHERE DATEPART(hh,StartDate) BETWEEN 8 AND 18
AND (DATEPART(w,StartDate)+@.@.DATEFIRST)%7 BETWEEN 2 AND 6

Razvan|||On 19 Jun, 12:56, Razvan Socol <rso...@.gmail.comwrote:

Quote:

Originally Posted by

Hello, Rob
>
Try something like this:
>
SELECT * FROM YourTable
WHERE DATEPART(hh,StartDate) BETWEEN 8 AND 18
AND (DATEPART(w,StartDate)+@.@.DATEFIRST)%7 BETWEEN 2 AND 6
>
Razvan

Thanks to you both...

A lot of help..

Regards

Rob

DateSerial in Access to SQL

Hi,
I need to convert this Access code to a Sql Case statement, but I'm not sure
exactly how...
IIf([Conversion Detail Table]!TXTDT<>0,DateSerial((Int(Left([Conversion
Detail Table]!TXTDT,4))),(Int(Mid([Conversion Detail
Table]!TXTDT,5,2))),(Int(Right([Conversi
on Detail
Table]!TXTDT,2)))),#10/1/2004#)
Can anyone help, thank you very much!
PatriceTry,
select
case when rtrim(TXTDT) != '0' then cast(left(8, TXTDT) as datetime) else
cast('20041001' as datetime) end
from
[Conversion Detail Table]
go
AMB
"Patrice" wrote:

> Hi,
> I need to convert this Access code to a Sql Case statement, but I'm not su
re
> exactly how...
>
> IIf([Conversion Detail Table]!TXTDT<>0,DateSerial((Int(Left([Conversion
> Detail Table]!TXTDT,4))),(Int(Mid([Conversion Detail
> Table]!TXTDT,5,2))),(Int(Right([Conversi
on Detail
> Table]!TXTDT,2)))),#10/1/2004#)
> Can anyone help, thank you very much!
> Patrice
>

dateserial eqivalent

hi all
what is an equivalent to VB's dateserial (if any)?
thanx,
erezNot sure what you want to do, but you can use convert.
For example
select convert(datetime, '2006-01-26')|||Erez Mor wrote:
> hi all
> what is an equivalent to VB's dateserial (if any)?
> thanx,
> erez
DateSerial takes three numeric arguments for year, month and day and
turns them into a date. There probably aren't many situations in SQL
Server when it would make sense to manipulate dates as three separate
numeric values. The easiest way to specify dates in code is as a
string:
SELECT CAST('20060126' AS DATETIME);
Always store dates as DATETIME or SMALLDATETIME.
If you need to do date arithmetic, use the DATEADD and DATEDIFF
functions.
If you still think you have a need to use three numerics for year,
month and day, try this:
SELECT
CAST(
RIGHT('0000'+CAST(2006 AS VARCHAR(4)),4)+
RIGHT('00'+CAST(1 AS VARCHAR(2)),2)+
RIGHT('00'+CAST(26 AS VARCHAR(2)),2)
AS DATETIME);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thanx a lot, folks
"David Portas" wrote:

> Erez Mor wrote:
> DateSerial takes three numeric arguments for year, month and day and
> turns them into a date. There probably aren't many situations in SQL
> Server when it would make sense to manipulate dates as three separate
> numeric values. The easiest way to specify dates in code is as a
> string:
> SELECT CAST('20060126' AS DATETIME);
> Always store dates as DATETIME or SMALLDATETIME.
> If you need to do date arithmetic, use the DATEADD and DATEDIFF
> functions.
> If you still think you have a need to use three numerics for year,
> month and day, try this:
> SELECT
> CAST(
> RIGHT('0000'+CAST(2006 AS VARCHAR(4)),4)+
> RIGHT('00'+CAST(1 AS VARCHAR(2)),2)+
> RIGHT('00'+CAST(26 AS VARCHAR(2)),2)
> AS DATETIME);
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Dates, stored procedures and asp.net

Hi,
I am having problems with dates and timestamps...

I have a textbox that allows the user to enter a date - the format is ccyymmdd. I have validation on this to ensure it is in the correct format.

I pass this as a parameter to a sql server stored procedure. In the stored procedure that parameter is defined as datetime and the field in the database it is also datetime.

To cut a long story short...an exception is being thrown! I have convinced myself it is due to the date - everything else I have used before. I am not sure whether I need to do a conversion in SQL perhaps prior to insert but not sure how to do this in any case, or, whether I have to do something in the vb code before loading the parameter??

CREATE PROCEDURE [dateexample]
(@.SomeDate [datetime])

AS INSERT INTO [testdate]
([SomeDate])

VALUES
(@.SomeDate)
GO

Anyone had similar problems / suggestions ? If this is not posted in an appropriate forum I apologise like I say I'm not sure which side the solution lies...

Thanks to all who take the time to read thistry changing your SP to this one..

CREATE PROCEDURE dateexample (@.SomeDate datetime)
AS
begin
INSERT INTO testdate VALUES (@.SomeDate)
end
GO

|||Is the exception being thrown by SQL Server, or by your vb code?|||Just to make sure, since you mention timestamps in your original post. I hope your problem isn't that you're trying to use a timestamp field to store a date/time.
A timestamp is not a date or time field. It's a serial number. The name is somewhat misleading. It's one of the unfortunate legacy things from back in the Sybase days.

From SQL Books-on-line...

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server? may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server? 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements.

Dates wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of wee
ks?
The tricky thing is that the script needs to account for ws where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17NH
Can you provide DDL+ sample data + expected result?
CREATE TABLE bbb
(
dt DATETIME,
...
....
)
"NH" <NH@.discussions.microsoft.com> wrote in message
news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
> Anyone got a script to get the start-dates and end-dates for x number of
> ws?
> The tricky thing is that the script needs to account for ws where the
> monday date is in say April and the Friday date is in May. In this case
> the
> start-date is the monday but the end-date may be a wednesday or whenever
> the
> last date of the month is. In other cases the start-date wont be the
> monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17|||What criterion/criteria determine whether your start-date is a Monday
or Wednesday?
If the end-date is a Wednesday, is the start-date then a Thursday?
Etc.
Andrew Watt [MVP]
On Mon, 24 Apr 2006 04:57:02 -0700, NH <NH@.discussions.microsoft.com>
wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
>I need a result set like this...
>Start-date End-date
>2006-01-30 2006-01-31
>2006-02-01 2006-02-03
>2006-02-06 2006-02-10
>2006-02-13 2006-02-17
>2006-02-20 2006-02-24
>2006-02-27 2006-02-28
>2006-03-01 2006-03-03
>2006-03-06 2006-03-10
>2006-03-13 2006-03-17|||this is the table that the results will go into...
CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
[key] [int] IDENTITY (1, 1) NOT NULL ,
[startdate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
I want to fill it with dates between 2006 and 2050.
Basically the start date will always be the monday, unless the any months
start date is some other day of the w. Same for end dates, they are
usually friday dates but a month end date could be a tuesday etc and this
needs to be recorded.
If you look at the sample data in my first post you should be able to see
the way it should work...? Does this make sense?
"Uri Dimant" wrote:

> NH
> Can you provide DDL+ sample data + expected result?
> CREATE TABLE bbb
> (
> dt DATETIME,
> ...
> .....
> )
>
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
>
>|||try this and let me know if this was what you wanted.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-30'
set @.enddate = '2006-03-17'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 1 and 5
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
drop table #temp|||A technical bug in my solution. date difference between 1 and 5 changed to 1
and 4.Use this.. updated.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-01'
set @.enddate = '2050-01-01'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 0 and 4
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
order by dateadd(dd, a.id,@.startdate)
drop table #temp|||NH
Read please this article helps you to get an idea
http://www.aspfaq.com/show.asp?id=2519
"NH" <NH@.discussions.microsoft.com> wrote in message
news:0CAF9FBF-E162-48FF-9661-971551CE2D1A@.microsoft.com...
> this is the table that the results will go into...
> CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
> [key] [int] IDENTITY (1, 1) NOT NULL ,
> [startdate] [datetime] NULL ,
> [enddate] [datetime] NULL
> ) ON [PRIMARY]
> I want to fill it with dates between 2006 and 2050.
> Basically the start date will always be the monday, unless the any months
> start date is some other day of the w. Same for end dates, they are
> usually friday dates but a month end date could be a tuesday etc and this
> needs to be recorded.
> If you look at the sample data in my first post you should be able to see
> the way it should work...? Does this make sense?
> "Uri Dimant" wrote:
>|||Hi all
Omnibuzz - there are some duplicates in that for me (e.g. 2006-04-03), but
I'm not sure what the problem is :(
Here's my stab... :)
--inputs
declare @.startdate datetime, @.enddate datetime
set @.startdate = '20060130'
set @.enddate = '20500101'
set datefirst 7
--calculation
declare @.NumberOfDays int
set @.NumberOfDays = datediff(d, @.startdate, @.enddate) + 1
set rowcount @.NumberOfDays
declare @.numbers table (i int identity(0,1), x bit)
insert into @.numbers select null from master.dbo.sysobjects a,
master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0
select d as StartDate,
case when datepart(day, d) = 1 --start of month
then dateadd(day, 6-datepart(dw, d), d)
when datepart(month, d) != datepart(month, d+4) --end of month
then dateadd(month, datediff(month, 0, d+4), 0)-1
else --normal w
d+4
end as EndDate
from
(select dateadd(dd, i, @.startdate) d from @.numbers) dates
where d = @.startdate --don't miss start date
or datepart(dw, d) = 2 --monday
or (datepart(day, d) = 1 and datepart(dw, d) between 3 and 6) --1st of
month and tue-fri
"Omnibuzz" wrote:

> A technical bug in my solution. date difference between 1 and 5 changed to
1
> and 4.Use this.. updated.
> select identity(int,0,1) as id into #temp from sysobjects
> declare @.startdate datetime, @.enddate datetime
> set @.startdate = '2006-01-01'
> set @.enddate = '2050-01-01'
> select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) a
s
> eow from #temp a, #temp b
> where
> datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
> between 0 and 4
> and (
> (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
> dateadd(dd, b.id,@.startdate)) = 6 )
> or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
> datepart(dd,dateadd(dd, b.id,@.startdate)) =
> datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
> b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-
' +
> cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
> datetime))))
> or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
> dateadd(dd, a.id,@.startdate)) = 1 ))
> and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd
,
> b.id,@.startdate))
> and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
> a.id,@.startdate) <= @.enddate
> order by dateadd(dd, a.id,@.startdate)
> drop table #temp|||Hi Ryan,
Thanks for pointing it out, though I haven't checked it yet. I am back
home now. Will check it and post the update, if necessary, tomorrow.|||On Mon, 24 Apr 2006 04:57:02 -0700, NH wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
(snip)
Hi NH,
Assuming that you already have a table of numbers in your database,
here's how you could fill your table quickly:
DECLARE @.StartOfPeriod smalldatetime
DECLARE @.EndOfPeriod smalldatetime
SET @.StartOfPeriod = '20060101'
SET @.EndOfPeriod = '20081231'
CREATE TABLE Ws
(StartDate smalldatetime NOT NULL PRIMARY KEY,
EndDate smalldatetime NOT NULL
)
-- Step 1: Generate full ws (mon-fri)
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(w, Number, '20050103'),
DATEADD(w, Number, '20050107')
FROM Numbers
WHERE DATEADD(w, Number, '20050103') >= @.StartOfPeriod
AND DATEADD(w, Number, '20050107') <= @.EndOfPeriod
-- Step 2a: For ws that span a month, add second partial w
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(day, 1 - DAY(EndDate), EndDate), EndDate
FROM Ws
WHERE MONTH(StartDate) <> MONTH(EndDate)
-- Step 2b: For ws that span a month, shorten first partial w
UPDATE Ws
SET EndDate = DATEADD(day, - DAY(EndDate), EndDate)
WHERE MONTH(StartDate) <> MONTH(EndDate)
SELECT * FROM Ws
go
Hugo Kornelis, SQL Server MVP

dates wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of weeks?
The tricky thing is that the script needs to account for weeks where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17Wrong forum, ignore this.
"NH" wrote:
> Anyone got a script to get the start-dates and end-dates for x number of weeks?
> The tricky thing is that the script needs to account for weeks where the
> monday date is in say April and the Friday date is in May. In this case the
> start-date is the monday but the end-date may be a wednesday or whenever the
> last date of the month is. In other cases the start-date wont be the monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,- 30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112
))
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.droptable.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it c
ame back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.droptable.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of[vbcol=seagreen]
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
James
Try:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
David Portas
SQL Server MVP
|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.sqlmonster.com
|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.sqlmonster.com
|||Thank you both.
|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>
|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>
|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...[vbcol=seagreen]
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
--
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
--
Message posted via http://www.sqlmonster.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
--
Message posted via http://www.sqlmonster.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
--
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
--
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> >I have a view that I'm building in which I need to get all records that
> >have
> > an InvoicedDate in in the past 30 days. So basically I set up part of
the
> > where clause as:
> >
> > (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> >
> > Which doesn't work because the function returns a date and a time,
whereas
> > I'm only storing dates in the InvoicedDate field. So, long story short,
> > it
> > doesn't get the dates from exactly 30 days ago. I could roll some kind
of
> > hack where I add one I guess, but that seems really hokey. What's a
> > better
> > way to accomplish this?
> >
> > Thanks!
> > James
> >
> >
>

Dates with century

Hi.
I am pulling dates from an AS400 where the date is formatted as 1050701 for
todays date. CYYMMDD.
I have tried to use the date format for the field (d), but the date does not
change.
Any ideas how I can format this type of date to display as MM/DD/YY.
Thanksi think you'll have to write some custom code to handle the AS400 date format
you're getting. in the cell that is displaying the date you just need
something like this: =code.FormatAsMMDDYY(AS400date)
read about writing custom code in BOL.
"Susan" wrote:
> Hi.
> I am pulling dates from an AS400 where the date is formatted as 1050701 for
> todays date. CYYMMDD.
> I have tried to use the date format for the field (d), but the date does not
> change.
> Any ideas how I can format this type of date to display as MM/DD/YY.
> Thanks

Dates when inserting a record

Is it possible to have sql server automatically record date and time (in a designated field)when a record is created in the db? This may seam basic but it has caused me a lot of grief.You can use database trigger for automatic firing.|||yes it can
just use when creating the table for that field a default

something like this:

create table xxx (
id int not null,
dateadded datetime not null default(getdate())
)|||so if I used get date () in the field and queried the table. Will it show me the date and time as of that momnet or will it have embedded the date and time the record was added...|||getdate() is a function that returns the current date when the record is actually added. it's not a column name. so if u want to query the date when the record was added, u need to query that column.|||getdate() is not working. It works in the sense that it displays system date and time. The problem is that it updates every column whenever I view the table or query it...

I need an option which will ensure that each record added inserts into the designated Table/Field the actual system time at which the record was inserted.|||Foefie's solution should work for you. Specifying the Default value of a column to be GETDATE() will insert the SQL Server's current system date/time into the column when the record is inserted into the database. It willl not fluctuate or vary with queries -- it is concretely written into the record. We use this approach all of the time without incident.

Terri|||If you use GETDATE() as default value in the column, you can control what happens.

What I mean by that is, if you want the date to reflect the datetime of last change, then you can include the field in any update, but specify DEFAULT as the value to be inserted. This will then cause the date to be update.

Alternatively (if you don't want it to change on every update, and always reflect the original datetime when the record was created), you should omit the field from any update statements and it will remain as originally inserted (you don't need to specify the field on any insert statements either, since on the first insert any fields for which you don't explicitly specify a value should get the default)

HTH

Anton|||Thank you. This explains it much better. The confusion has been due to the fact that while using Enterprise Manager, rightclicking on the table in question and selecting return all rows, I have noticed that it always updates the date and time. I just assumed that the same would apply anytime I queried the data in the table.

Thanks!

Dates Result Set

Hi, All,
I need a single result set that just returns today's date
plus 6 more days. I want to do this without a cursor.
Any ideas?
Thanks!
T
Here is one solution:
select cast(convert(char(8),getdate(),112) as datetime) + i as d
from (
select 0 as i union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
) T
Steve Kass
Drew University
tom sawyer wrote:

>Hi, All,
>I need a single result set that just returns today's date
>plus 6 more days. I want to do this without a cursor.
>Any ideas?
>Thanks!
>T
>

Dates rendering incorrectly

Hi,I have a strange problem occuring on a scatter graph. The graphs x axis are susposed to be dates. But if there is no information on that day then the label displays an internal date format (number of days since 1/1/1970 I believe)I have a screenshot here:http://members.iinet.net.au/~y0da/dotnet/mechprop.GIFDoes anyone have any suggestions how to stop this occuring?

you could use an ISNULL function in your sql. something like :
SELECT
...
ISNULL(datecolumn,'')
FROM
...
and if you are using Date functions you could substitute 0 for null or empty values.

|||The sql is not outputing the suspect dates, its reporting services. There are no records coming from the db for those dates
|||I prbly didnt understand what you are trying to do but Reporting Services by itself does not produce anything. It only displays the data in the format you specify. If you post the query you have and how you are handling the number of days I can try to help you better.

Dates Query

Hi
We have a work schedule table in our database that comprises of an Employee
ID, a Start Date/Time and a Finish Date/Time.
We are trying to design a query that will return the record every Monday
that it is operational.
I.e. if a record existed with the following data:
Employee Start Date/Time Finish Date/Time
Smith 4th April 2005 10:00 26th April 2005 17:00
Jones 18th April 2005 12:00 18th April 2005 16:00
the data would be returned from the query as follows
Smith 4th April 2005
Smith 11th April 2005
Smith 18th April 2005
Smith 26th April 2005
Jones 18th April 2005
5 records returned
From this i will then be able to design a function that displays how many
hours were spent on the activity each w.
Is there anyway of getting a query to do this, or is it something that i
will just have to do in VB with an array?
Thanks in advance for any help.You need a calendar table (see http://www.aspfaq.com/show.asp?id=2519) and
then it simply boils down to:
SELECT e.Employee, c.dt
FROM calendar c
INNER JOIN WorkSchedule ws
ON c.dt BETWEEN CONVERT(CHAR(8), ws.StartDate, 112) AND CONVERT(CHAR(8),
ws.EndDate, 112)
WHERE c.dayname = 'Monday'
Jacco Schalkwijk
SQL Server MVP
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
> Employee ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
Example:
select ws.employee, ws.start
from work_schedule as ws inner join dbo.calendar as c
on c.dt >= convert(char(8), ws.start, 112) and c.dt < dateadd(day, 1,
convert(char(8), ws.finish, 112))
where c.dt = 'monday'
AMB
"Chris" wrote:

> Hi
> We have a work schedule table in our database that comprises of an Employe
e
> ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Chris
My two cents
CREATE TABLE #dates
(
col CHAR(1) NOT NULL,
sdt DATETIME NOT NULL,
fdt DATETIME NOT NULL
)
INSERT INTO #dates VALUES ('A','20050404','20050426')
SELECT DISTINCT col,dt FROM
(
SELECT col,DATEADD(day,num,'20040331')dt FROM Numbers,#dates
WHERE num <31
) AS D WHERE DATEPART(w,dt)=1
--See Steve Kass's example to find a first,second... monday in the month
create function dbo.NthWDay(
@.first datetime, -- First of the month of interest (no time part)
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of w we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(wday,@.result))%7
end
go
-- Find the 5th Thursday of August, 2002
select dbo.NthWDay('2002/08/01',5,5) as D
select datename(wday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWDay('2002/08/01',5,5) as D
) X
go
drop function NthWDay
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
Employee
> ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Thanks everyone
I had thought about creating a sort of calendar table, but didnt know
whether this was good practise, or the best way of doing it.
I guess it is.
Thanks again
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
> Employee ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Thanks everyone
I had thought about creating a sort of calendar table, but didnt know
whether this was good practise, or the best way of doing it.
I guess it is.
Thanks again
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
> Employee ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>

Dates problem in SQL Server Evrywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Replied in another thread (same question)|||Replied in another thread. (for same question)

Dates problem in SQL Server Everywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

Please use quote for inserting datetime value, it will solve the problem.

use like

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,'4/7/2006',DEFAULT)

Thanks

Sachin

|||

Thank you,

The information was very good,

the problem with the dates is now solved.

Toni.

Dates problem in SQL Server Everywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Repliedin another thread (same question)