Datetime changes when insert

Hi,

I have one table with data that has two datetime columns. I also have an empty table that is i copy of the first (ie identical design) + one column, that I want to move some rows to.

When I do the insert into the empty table the datetime fileds are converted from '2004-01-01' to 'Jan 1 2004 12:00AM' WHY!!!

I hate this kind of problems... I haven't told it to convert, and besides it adds a time too...

Anyone got a solution or explanation for this, please?I don't think it's been converted. It's just the way you display it. The value stays the same.

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 parameters

I create a report that brings information from an informix database. I have a
problem with date parameters when I put them static on query design (with
date format mm/dd/yyyy) it retreives information on a good response time. But
if I preview report with the option to add date paramaters reports doesn't
display information it always says "Processing report". I think that the
problem is that SQL adds the time to parameter because if I add time to date
value on design mode it sends an error.
Please let me know if you have comments
I will appreciate it
thanksThe date parameter is definitely date/time. If the data type in your
informix database just holds date then you need to use one of the date
functions and strip off the time. Go to your dataset and click on the ...
and go to the parameters tab. You will see the query parameter that maps to
the report parameter, change the report parameter to an expression and have
the expression convert it to just the date. You can use any of the functions
in Microsoft.VisualBasic, System.Convert, and System.Math without have to
write any custom code. Read up on expressions.
Bruce L-C
"Yoya" <Yoya@.discussions.microsoft.com> wrote in message
news:0CDE2FB6-C49F-47AB-B11D-40B54FB32965@.microsoft.com...
> I create a report that brings information from an informix database. I
have a
> problem with date parameters when I put them static on query design (with
> date format mm/dd/yyyy) it retreives information on a good response time.
But
> if I preview report with the option to add date paramaters reports doesn't
> display information it always says "Processing report". I think that the
> problem is that SQL adds the time to parameter because if I add time to
date
> value on design mode it sends an error.
> Please let me know if you have comments
> I will appreciate it
> thanks
>