datetime question

Hi,
I am using SQL Server 2000 SP3 on Windows 2000 Server. I have tried the
following
declare @.d1 as datetime, @.d2 as datetime
--Query Set#1
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.001'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#1 Results
yes
yes
--Query Set#2
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.002'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#2 Results
no
yes
--Query Set#3
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:01:00.000'
if @.d1 = @.d2 print 'yes' else print 'no'
if @.d1 like @.d2 print 'yes' else print 'no'
--Query Set#3 Results
no
no
I am positive that these are not anomalies. So, if someone could explain
these results, it would be great.Hi
LIKE from BOL:
"If any of the arguments are not of character string data type, Microsoft
SQL ServerT converts them to character string data type, if possible."
A LIKE does string comparisons. It uses the default CONVERT to String data
type so:
declare @.d1 as datetime, @.d2 as datetime
set @.d1='03/26/1995 01:00:00.000'
set @.d2='03/26/1995 01:00:00.002'
select convert(char(30), @.d1)
select convert(char(30), @.d2)
Results in:
Mar 26 1995 1:00AM
Mar 26 1995 1:00AM
If you need to do like on non-string datatypes, tell SQL exactly how to
convert the data.
select convert(char(30), @.d1, 113)
select convert(char(30), @.d2, 113)
Results in:
26 Mar 1995 01:00:00:000
26 Mar 1995 01:00:00:003
That will give correct results.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"payyans" <payyans@.discussions.microsoft.com> wrote in message
news:54E90AE2-D1FD-42A1-A4AE-D19F9A0F3A93@.microsoft.com...
> Hi,
> I am using SQL Server 2000 SP3 on Windows 2000 Server. I have tried the
> following
> declare @.d1 as datetime, @.d2 as datetime
> --Query Set#1
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.001'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#1 Results
> yes
> yes
> --Query Set#2
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.002'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#2 Results
> no
> yes
> --Query Set#3
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:01:00.000'
> if @.d1 = @.d2 print 'yes' else print 'no'
> if @.d1 like @.d2 print 'yes' else print 'no'
> --Query Set#3 Results
> no
> no
> I am positive that these are not anomalies. So, if someone could explain
> these results, it would be great.|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> LIKE from BOL:
> "If any of the arguments are not of character string data type, Microsoft
> SQL ServerT converts them to character string data type, if possible."
> A LIKE does string comparisons. It uses the default CONVERT to String data
> type so:
> declare @.d1 as datetime, @.d2 as datetime
> set @.d1='03/26/1995 01:00:00.000'
> set @.d2='03/26/1995 01:00:00.002'
> select convert(char(30), @.d1)
> select convert(char(30), @.d2)
> Results in:
> Mar 26 1995 1:00AM
> Mar 26 1995 1:00AM
> If you need to do like on non-string datatypes, tell SQL exactly how to
> convert the data.
> select convert(char(30), @.d1, 113)
> select convert(char(30), @.d2, 113)
> Results in:
> 26 Mar 1995 01:00:00:000
> 26 Mar 1995 01:00:00:003
> That will give correct results.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "payyans" <payyans@.discussions.microsoft.com> wrote in message
> news:54E90AE2-D1FD-42A1-A4AE-D19F9A0F3A93@.microsoft.com...
>
>

datetime convertion in SqlServer 2000

SqlServer 2000 with SP3. RepTime is a datetime in TableA. I ran the two commands beblow:

SELECT distinct CAST([RepTime] AS INT) FROM TableA

39004
39002
39003

select max(RepTime),min(RepTime) from TableA

2006-10-16 10:36:03.940 2006-10-13 17:32:00.080

From 2006-10-13 to 2006-10-16, there are four days. But I got three distinct int from it. Anyone knows ?

Maybe you have two entries having the same date but different times?

DECLARE @.test AS TABLE (dat datetime);

INSERT into @.test values ('2006-12-19')
INSERT into @.test values ('2006-12-20')
INSERT into @.test values (GETDATE())

-- This retrieves 3 columns
SELECT DISTINCT dat FROM @.test

-- This retrieves 2 columns
SELECT DISTINCT CAST(dat AS INT) FROM @.test

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

|||

Very interesting while dig in to your issue..

SQL Server Converts the date using the following logic...

Declare @.Date as Datetime
Select @.Date = '2006-10-16 10:00:00'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 11:59:59'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 12:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-16 23:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-17 10:00:00'
select Cast(@.Date as Int) -- Result : 39005

Understood, the Integer cast will take one day from 12:00:00 PM to 11:59:59 AM (Strange Buddy = Bcs, after 12AM the value will be >= 39004.5 when the float number converted to integer it will round off the value...)

To overcome this issue use the following query..

SELECT distinct CAST(Convert(Datetime,Convert(Varchar,[RepTime],101)) AS INT) FROM TableA

Here we are omiting the Time field completly..

|||

Lucky P wrote:

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

Nope... Time is big concern here Lucky P

|||

You're completely right...

When converting to a Decimal Value, the Date '2006-10-16 12:00:00' is retrieved as 39004.5, which is rounded up to 39005 when converting it to INT.....

I made the mistake because im running the query before 12:00

|||

Yes absolutly it is round off issue... ..

So we can overcome this issue by the following query ..

SELECT distinct Cast(Round(CAST([RepTime] AS Float),0,2) as INT) FROM TableA

|||I got it. I made the same mistake too. Thanks to you all!|||

I use this one now:

SELECT distinct floor(cast([RepTime] as float)) FROM TableA

DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10

DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10