Create a table with a field with only Time datatype

Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?

See this enigma's post link (http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk)
Its a good one ...|||i dont understand why you are not thinking in terms of a datetime/smalldatetime field by ignoring the date part and whats wrong in it. however, if you are rigid you can store it either as char field or as numeric field (float/decimal). remember you do not actually save anything by doing so.

storing as char field is straight forward. if you want to store as number (remember that each date+time has a numeric representation) can take hints from the following queries

--1 minute as number
select cast(cast('20060101 11:01 AM' as smalldatetime) as float)- cast(cast('20060101 11:00 AM' as smalldatetime) as float)
--it returns approx 0.0006944

--use that to get a date time 20060101 11:00 AM back
select cast( cast(cast('20060101' as smalldatetime) as float) + (0.0006944*60*11) as smalldatetime)

quite unnecessary...;-)sql

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

Create a datetime dimension table with start and end dates

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

CREATE TABLE DimensionTable

(

IdCol INT IDENTITY(1,1),

Timecol DATETIME

)

GO

DECLARE @.Startdate DATETIME

DECLARE @.Enddate DATETIME

SET @.Startdate = '20040101'

SET @.Enddate = '20080101'

WHILE @.Startdate < @.Enddate

BEGIN

INSERT INTO DimensionTable

(

TimeCol

)

SELECT @.Startdate

SET @.Startdate = DATEADD(mi,1,@.Startdate)

END

That could take some time :-)

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot

Coverting Int to DateTime Recommendation?

Friends,

I have a database that I am in the process of converting to a new application. All the dates are currently stored as an numeric YYYYMMDD format. I am hoping someone can suggest the best way of dealing with these in SQL Server. Should I convert them to DateTime formats for example, and what is the best way to do this?

Any input would be greatly appreciated.

J.H.

yes you should do it.

the best way?

this is how I would do it -

create function YYYMMDDToDate(@.date int)
returns datetime
as
begin
declare @.datestring char(8)
set @.datestring = cast(@.date as char(8))
return convert(datetime, substring(@.datestring,1,4) +'-' +
substring(@.datestring,5,2) +'-' +
substring(@.datestring,7,2), 127)
end
go
alter table affectedtable add newDateColumn datetime;
go
update affectedtable
set newDateColumn = dbo.YYYMMDDToDate(oldDateColumn);
alter table affectedtable drop column oldDateColumn;
exec sp_rename 'dbo.affectedtable.newDateColumn', 'oldDateColumn', 'COLUMN';

|||

The trick is that MSSQL recognizes dates in YYYYMMDD format unambigously. So may just convert it twice as:

select cast(cast(20060104 as varchar) as datetime)

Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/defaul...NoWebContent=1.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/defaul...NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
2
> 2004-02-12.
> to
to
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
>
http://support.microsoft.com/defaul...NoWebContent=1.
being
> 2
trying
> to
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
number
> in
> is
in
datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
> being
> trying
opposed
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> will
> use
datetime?
> (IMO,
>
http://groups.google.com/groups?oi=...ublic.sqlserver
date
> number
Something
wrote
> in
> datetime),
>
http://support.microsoft.com/defaul...NoWebContent=1.
shows
> opposed
>

Coverting float to datetime

For some reason if I do a convert to a datetime, the time ends up being 2
days off. For example if I do a cast or convert of 38027 I get 2004-02-12.
If I put that same value in excel and make the field date, it shows
2004-02-10. The 10th is the correct date and its making me crazy trying to
figure out what the problem is. I have tried using decimal as opposed to
float and I still get the same results. Any ideas out there?James,
Two days difference come because of two reasons:
1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
while Excel as 1
2. Excel incorrectly assumes 1900 is a leap year, as described at
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> For some reason if I do a convert to a datetime, the time ends up being 2
> days off. For example if I do a cast or convert of 38027 I get
2004-02-12.
> If I put that same value in excel and make the field date, it shows
> 2004-02-10. The 10th is the correct date and its making me crazy trying
to
> figure out what the problem is. I have tried using decimal as opposed to
> float and I still get the same results. Any ideas out there?
>|||But I am using Excel XP and Excel is the one that is reporting the date
correctly. If I do select convert(float,getdate()) and paste that number in
excel, convert the field to date, it show it as two days ago. Something is
amiss.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> James,
> Two days difference come because of two reasons:
> 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> while Excel as 1
> 2. Excel incorrectly assumes 1900 is a leap year, as described at
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > For some reason if I do a convert to a datetime, the time ends up being
2
> > days off. For example if I do a cast or convert of 38027 I get
> 2004-02-12.
> > If I put that same value in excel and make the field date, it shows
> > 2004-02-10. The 10th is the correct date and its making me crazy trying
> to
> > figure out what the problem is. I have tried using decimal as opposed
to
> > float and I still get the same results. Any ideas out there?
> >
> >
>|||Why do you expect that converting to some strange float representation will
result in a meaningful value? Especially assuming that two products will use
the same rules when converting from some strange float value to datetime?
Excel and SQL Server uses different internal representation of datetime.
This is why you never should rely on the internal representation of the
datetime datatype. Always use the supported character representation. (IMO,
SQL Server shouldn't even allow for such CAST or CONVERT...)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> But I am using Excel XP and Excel is the one that is reporting the date
> correctly. If I do select convert(float,getdate()) and paste that number
in
> excel, convert the field to date, it show it as two days ago. Something
is
> amiss.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > James,
> >
> > Two days difference come because of two reasons:
> > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS datetime),
> > while Excel as 1
> > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > Solid Quality Learning
> > More than just Training
> > www.SolidQualityLearning.com
> >
> > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > For some reason if I do a convert to a datetime, the time ends up
being
> 2
> > > days off. For example if I do a cast or convert of 38027 I get
> > 2004-02-12.
> > > If I put that same value in excel and make the field date, it shows
> > > 2004-02-10. The 10th is the correct date and its making me crazy
trying
> > to
> > > figure out what the problem is. I have tried using decimal as opposed
> to
> > > float and I still get the same results. Any ideas out there?
> > >
> > >
> >
> >
>|||Thanks for nothing. That was real helpful.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > But I am using Excel XP and Excel is the one that is reporting the date
> > correctly. If I do select convert(float,getdate()) and paste that
number
> in
> > excel, convert the field to date, it show it as two days ago. Something
> is
> > amiss.
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > James,
> > >
> > > Two days difference come because of two reasons:
> > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
datetime),
> > > while Excel as 1
> > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Associate Mentor
> > > Solid Quality Learning
> > > More than just Training
> > > www.SolidQualityLearning.com
> > >
> > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > For some reason if I do a convert to a datetime, the time ends up
> being
> > 2
> > > > days off. For example if I do a cast or convert of 38027 I get
> > > 2004-02-12.
> > > > If I put that same value in excel and make the field date, it shows
> > > > 2004-02-10. The 10th is the correct date and its making me crazy
> trying
> > > to
> > > > figure out what the problem is. I have tried using decimal as
opposed
> > to
> > > > float and I still get the same results. Any ideas out there?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Agree with Tibor, just to add - have you checked the link in the bullet 2.
of my previous answer? MS says there is bug in Execl. Chek on some calendar,
1900 really was not a leap year.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> Why do you expect that converting to some strange float representation
will
> result in a meaningful value? Especially assuming that two products will
use
> the same rules when converting from some strange float value to datetime?
> Excel and SQL Server uses different internal representation of datetime.
> This is why you never should rely on the internal representation of the
> datetime datatype. Always use the supported character representation.
(IMO,
> SQL Server shouldn't even allow for such CAST or CONVERT...)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > But I am using Excel XP and Excel is the one that is reporting the date
> > correctly. If I do select convert(float,getdate()) and paste that
number
> in
> > excel, convert the field to date, it show it as two days ago. Something
> is
> > amiss.
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > James,
> > >
> > > Two days difference come because of two reasons:
> > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
datetime),
> > > while Excel as 1
> > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Associate Mentor
> > > Solid Quality Learning
> > > More than just Training
> > > www.SolidQualityLearning.com
> > >
> > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > For some reason if I do a convert to a datetime, the time ends up
> being
> > 2
> > > > days off. For example if I do a cast or convert of 38027 I get
> > > 2004-02-12.
> > > > If I put that same value in excel and make the field date, it shows
> > > > 2004-02-10. The 10th is the correct date and its making me crazy
> trying
> > > to
> > > > figure out what the problem is. I have tried using decimal as
opposed
> > to
> > > > float and I still get the same results. Any ideas out there?
> > > >
> > > >
> > >
> > >
> >
> >
>|||I was trying to pass the message that using anything other than a character
representation will eventually lead to these type of situations. AFAIK, SQL
Server doesn't even document what conversion from datetime to float will be.
In other words, they could change that in the next version.
My apologies if the post came out as unhelpful or rude. That was not my
intent.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
news:%23DVtmd89DHA.1268@.TK2MSFTNGP12.phx.gbl...
> Thanks for nothing. That was real helpful.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uTlYuC89DHA.2368@.TK2MSFTNGP11.phx.gbl...
> > Why do you expect that converting to some strange float representation
> will
> > result in a meaningful value? Especially assuming that two products will
> use
> > the same rules when converting from some strange float value to
datetime?
> > Excel and SQL Server uses different internal representation of datetime.
> > This is why you never should rely on the internal representation of the
> > datetime datatype. Always use the supported character representation.
> (IMO,
> > SQL Server shouldn't even allow for such CAST or CONVERT...)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > news:uMJxS579DHA.3488@.tk2msftngp13.phx.gbl...
> > > But I am using Excel XP and Excel is the one that is reporting the
date
> > > correctly. If I do select convert(float,getdate()) and paste that
> number
> > in
> > > excel, convert the field to date, it show it as two days ago.
Something
> > is
> > > amiss.
> > >
> > >
> > > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si>
wrote
> in
> > > message news:uRRTbs79DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > > James,
> > > >
> > > > Two days difference come because of two reasons:
> > > > 1. SQL Server assumes 1.1.1900 as 0 - check SELECT CAST(0 AS
> datetime),
> > > > while Excel as 1
> > > > 2. Excel incorrectly assumes 1900 is a leap year, as described at
> > > >
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q181/3/70.asp&NoWebContent=1.
> > > >
> > > > --
> > > > Dejan Sarka, SQL Server MVP
> > > > Associate Mentor
> > > > Solid Quality Learning
> > > > More than just Training
> > > > www.SolidQualityLearning.com
> > > >
> > > > "James Jeffers" <james.jeffers@.emersonprocess.com> wrote in message
> > > > news:#hqEgd79DHA.4080@.tk2msftngp13.phx.gbl...
> > > > > For some reason if I do a convert to a datetime, the time ends up
> > being
> > > 2
> > > > > days off. For example if I do a cast or convert of 38027 I get
> > > > 2004-02-12.
> > > > > If I put that same value in excel and make the field date, it
shows
> > > > > 2004-02-10. The 10th is the correct date and its making me crazy
> > trying
> > > > to
> > > > > figure out what the problem is. I have tried using decimal as
> opposed
> > > to
> > > > > float and I still get the same results. Any ideas out there?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

covert this C# to Vb.Net for Code

Hello,

This is my code in C#

DateTime sYear = new DateTime(Now(),1,1);

DateTime q1Start = sYear;

DateTime q2Start = sYear.AddMonths(3);

DateTime q3Start = sYear.AddMonths(6);

DateTime q4Start = sYear.AddMonths(9);

DateTime q1End = q2Start.AddDays(-1);

DateTime q2End = q3Start.AddDays(-1);

DateTime q3End = q4Start.AddDays(-1);

DateTime q4End = sYear.AddYears(1).AddDays(-1);

Can anyone convert this to VB.Net so that I can use it in "Code" section of the Reporting Services. I tried a VB-C# converter but Reporting Services did not like DateTime datatype.

Any help?

Try the following:

Dim sYear As New System.DateTime(Now(),1,1)
Dim q1Start As System.DateTime = sYear
Dim q2Start As System.DateTime = sYear.AddMonths(3)
Dim q3Start As System.DateTime = sYear.AddMonths(6)
Dim q4Start As System.DateTime = sYear.AddMonths(9)
Dim q1End As System.DateTime = q2Start.AddDays(-1)
Dim q2End As System.DateTime = q3Start.AddDays(-1)
Dim q3End As System.DateTime = q4Start.AddDays(-1)
Dim q4End As System.DateTime = sYear.AddYears(1).AddDays(-1)

HTH

|||That conversion was not that hard. Just a pointer, most of the time all you have to do is remove the ; from the c# code but there is quite a bunch of stuff that you still have to convert.|||

Here is a link you might find very hand to convert in either direction:

http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx

counting the years in multiple rows question

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :

Code Snippet

SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))

but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))

P.S i want to be able to use it in a where clause :

Code Snippet

select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@.Exp)

thanks in advance

You have to use the grouping together with the having clause:

declare @.Exp as int

select max(A) as A, max(B) as B from jobap

where JobAp.ind = JobApExp.JobAp

HAVING SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > @.Exp

|||

create table #Personnel(id int,name varchar(20))
insert #Personnel select 1,'madhu'
insert #Personnel select 2 ,'zyx'

create table #PersonnelExp (ID int,fmdt datetime,todt datetime)
delete #PersonnelExp
insert #PersonnelExp select 1,'1990-1-1','1999-1-1'
insert #PersonnelExp select 1,'1999-1-2','2002-1-2'
insert #PersonnelExp select 1,'2003-1-2','2007-1-2'
insert #PersonnelExp select 2,'1995-1-1','1999-1-1'
insert #PersonnelExp select 2,'1999-1-2','2004-1-2'
insert #PersonnelExp select 2,'2005-1-2','2007-1-2'

select a.ID,a.Name,b.TotExp From #Personnel a,
(select id,SUM(DATEDIFF(year , fmdt, todt )) As TotExp from #PersonnelExp group by id) b
where a.ID=b.ID
and totexp>12

Check this script

Madhu

|||

You may want to consider performing the DATEDIFF on the day or month level, as the year shows the year difference between the two dates.

IE

select datediff(yy, '01/01/2006', '12/31/2006')

select datediff(yy, '12/31/2006', '01/01/2007')

The first returns 0, even though it's a full year. And the second returns 1 even though it's only been 1 day.