Datetime Column Problem

Hi All,
Here is my situation:
I have a table which has a column of type datetime and it carries data
with timestamp in it. For example: 2004-08-16 16:09:56.120
I have another column which is also of type datetime but contains data
with no time values (because someone didn't pay much attention). For
example: 2004-08-16 00:00:00.000
Here is my problem:
I have about 50 stored procs where these columns are compared for
example: subj_svd_visit_date < subj_budget_start_date etc.
What is the best way to approach this problem so that I don't have to
make change in the 50 procs.
Just to note that in some places people are using GetDate() when they
are inserting values into these columns.
Thanks very much for your input.
*** Sent via Developersdex http://www.examnotes.net ***You can start here:
http://www.karaszi.com/sqlserver/info_datetime.asp
-oj
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:eHG$nxVQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> Here is my situation:
> I have a table which has a column of type datetime and it carries data
> with timestamp in it. For example: 2004-08-16 16:09:56.120
> I have another column which is also of type datetime but contains data
> with no time values (because someone didn't pay much attention). For
> example: 2004-08-16 00:00:00.000
> Here is my problem:
> I have about 50 stored procs where these columns are compared for
> example: subj_svd_visit_date < subj_budget_start_date etc.
> What is the best way to approach this problem so that I don't have to
> make change in the 50 procs.
> Just to note that in some places people are using GetDate() when they
> are inserting values into these columns.
> Thanks very much for your input.
> *** Sent via Developersdex http://www.examnotes.net ***|||I don't see the problem. This is still a valid datetime value: 2004-08-16
00:00:00.000. It simply has a time of midnight. All comparisons and such
are still very much valid against that value.
Andrew J. Kelly SQL MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:eHG$nxVQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> Here is my situation:
> I have a table which has a column of type datetime and it carries data
> with timestamp in it. For example: 2004-08-16 16:09:56.120
> I have another column which is also of type datetime but contains data
> with no time values (because someone didn't pay much attention). For
> example: 2004-08-16 00:00:00.000
> Here is my problem:
> I have about 50 stored procs where these columns are compared for
> example: subj_svd_visit_date < subj_budget_start_date etc.
> What is the best way to approach this problem so that I don't have to
> make change in the 50 procs.
> Just to note that in some places people are using GetDate() when they
> are inserting values into these columns.
> Thanks very much for your input.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks oj for the link, it is very helpful.
Thanks Kelly for looking into the problem. You are right that there is
no problem on the surface but the time that one of the field is storing
is not needed. That is what my question was. Given that now one of the
field stores time value that is not needed, what do I do to get rid off
it and what do I do to the code that compares it.
*** Sent via Developersdex http://www.examnotes.net ***|||Not sure I understand what you are asking. The Link OJ posted should answer
most questions about using datetime. If you are asking how to make all
datetime values store midnight and retain the date portion you can do
something like this:
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),Your
DateTimeCol,112))
Andrew J. Kelly SQL MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:ezgs55ZQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Thanks oj for the link, it is very helpful.
> Thanks Kelly for looking into the problem. You are right that there is
> no problem on the surface but the time that one of the field is storing
> is not needed. That is what my question was. Given that now one of the
> field stores time value that is not needed, what do I do to get rid off
> it and what do I do to the code that compares it.
>
> *** Sent via Developersdex http://www.examnotes.net ***

datetime

i'm using datediff to get the elapsed time b/t a timestamp and the current time. at this point i'm putting the answer in minutes, but i would like to format it to be similar to HH:MM.
how do i do this in sql server??
thanks in advance
e3wittselect cast(datediff(mi,'05/28/2004',getdate())/60 as varchar)+':'
+cast(datediff(mi,'05/28/2004',getdate())-(datediff(mi,'05/28/2004',getdate())/60)*60 as varchar)|||SET ANSI_NULLS OFF
SET NOCOUNT ON
GO

if object_id(N'dbo.fn_ElapsedTime') is not null begin
drop function dbo.fn_ElapsedTime
print 'Function dbo.fn_ElapsedTime dropped'
end
go

CREATE function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end
go

if object_id(N'dbo.fn_ElapsedTime') is not null begin
print 'Function dbo.fn_ElapsedTime created'
end
go|||ok... now it's working just the way i was wanting.

thank you.|||Don't forget about the modulo operator (%). It's hand for converting time values:

select cast(datediff(mi, [TimeStamp], getdate())/60 as int) + ':' + (datediff(mi, [TimeStamp], getdate()) % 60)

datediff problems.

Hi all, I have quite a conundrum, at least for me.

I need to get the difference in minutes between the current date and a timestamp. However, I have two timestamp fields. The first one could be NULL (TIMESTAMP_1). The second one is never NULL(TIMESTAMP2). What I want to do is say give me the datediff between the max of TIMESTAMP_1 or TIMESTAMP_2 and the current time.

This is what I tried to select:

datediff(mi,max(isnull(TIMESTAMP_1,TIMESTAMP_2)),g etdate())

However, it grabs TIMESTAMP_1 if it's there and if not then it grabs TIMESTAMP_2. How can I tell it to take the max of both?

This is Sybase ASE 12.5.

Thanks for any help.well this is using sql server so code might be a bit different but same concept should be able to be used.

case when timestamp1 > timestamp2 then DATEDIFF(mi, timestamp1, getdate())
when timestamp1 < timestamp2 then DATEDIFF(mi, timestamp2, getdate())
ELSE DATEDIFF(mi, timestamp1, getdate()) END

DateDiff function problem

Hi Folks,
Looking for assistance on using the datediff command to convert a date\time
stamp into unix time. A few of you where a great help the last time
directing me in the right direction on doing this. My problem is that the
time stamp is one hour out (one hour ahead) . The below syntax is how
capturing the date and time stamp and converting. I have check my clocking
setting on my server and anything seems fine. I working with British\Irsh
time zone. Anyone any idea where I'm going wrong?
SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
Many thanks,
Liam.Hi
The 1 hour could be due to Summer Time / Daylight Savings Time.
What if you change your server to be exact GMT?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Liam Mac" wrote:

> Hi Folks,
> Looking for assistance on using the datediff command to convert a date\tim
e
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Well, do you observe daylight savings time? Is your machine set up to do
so? Does SELECT GETDATE() yield the right date/time?
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B197644A-1531-4CB6-A394-69F47FAD74C2@.microsoft.com...
> Hi Folks,
> Looking for assistance on using the datediff command to convert a
> date\time
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Or just use GETUTCDATE() instead?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> Hi
> The 1 hour could be due to Summer Time / Daylight Savings Time.
> What if you change your server to be exact GMT?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Liam Mac" wrote:
>
date\time
the
clocking
British\Irsh|||thanks folks, below sytnax worked.
"Adam Machanic" wrote:

> Or just use GETUTCDATE() instead?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> date\time
> the
> clocking
> British\Irsh
>
>

Date/Time truncation

I have the following SQL query that I want to drop the timestamp from the results:

select pos_trn_ln.trn_dt,
pos_trn_ln.store_cd, pos_trn_ln.term_num,
pos_trn_ln.trn_num, pos_trn.cshr_num, pos_trn_ln.ln_tp,
pos_trn_ln.amt, pos_trn_ln.sku_num, gm_itm.UDF1, pos_trn_ln.qty
from pos_trn_ln, pos_trn, gm_itm, gm_sku
where pos_trn_ln.trn_num=pos_trn.trn_num
and gm_sku.sku_num=pos_trn_ln.sku_num
and gm_sku.itm_cd=gm_itm.itm_cd
and pos_trn_ln.trn_dt between '1-DEC-2003'
and '1-DEC-2003'
and pos_trn_ln.ln_tp in ('EMP')

The results look as follows:
TRN_DT STORE_CD TERM_NUM TRN_NUM CSHR_NUM LN_TP AMT SKU_NUM UDF1 QTY
------ --- --- --- --- -- ---- ---- --- ---
2003-12-01 00:00:00 0004 02 147 005555 EMP -11 971600976-03 NI 1
2003-12-01 00:00:00 0023 01 348 000000 EMP -4 000027160-06 CM 1

...I want to drop off the time stamp all together in the results. I thought using the TRUNC function would work like:

where pos_trn_ln.trn_dt=TRUNC(datefield) but something is wrong. Anyone?You didn't state what database your using but there is available a DATE() or TO_DATE() function in which you can also specify formatting to pull only the date.

Originally posted by heprox
I have the following SQL query that I want to drop the timestamp from the results:

select pos_trn_ln.trn_dt,
pos_trn_ln.store_cd, pos_trn_ln.term_num,
pos_trn_ln.trn_num, pos_trn.cshr_num, pos_trn_ln.ln_tp,
pos_trn_ln.amt, pos_trn_ln.sku_num, gm_itm.UDF1, pos_trn_ln.qty
from pos_trn_ln, pos_trn, gm_itm, gm_sku
where pos_trn_ln.trn_num=pos_trn.trn_num
and gm_sku.sku_num=pos_trn_ln.sku_num
and gm_sku.itm_cd=gm_itm.itm_cd
and pos_trn_ln.trn_dt between '1-DEC-2003'
and '1-DEC-2003'
and pos_trn_ln.ln_tp in ('EMP')

The results look as follows:
TRN_DT STORE_CD TERM_NUM TRN_NUM CSHR_NUM LN_TP AMT SKU_NUM UDF1 QTY
------ --- --- --- --- -- ---- ---- --- ---
2003-12-01 00:00:00 0004 02 147 005555 EMP -11 971600976-03 NI 1
2003-12-01 00:00:00 0023 01 348 000000 EMP -4 000027160-06 CM 1

...I want to drop off the time stamp all together in the results. I thought using the TRUNC function would work like:

where pos_trn_ln.trn_dt=TRUNC(datefield) but something is wrong. Anyone?|||I apologize, the DB is an Oracle 8i instance. When you say use the DATE() function, how? I've tried TO_DATE() with invalid column name errors...|||TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'))

Originally posted by heprox
I apologize, the DB is an Oracle 8i instance. When you say use the DATE() function, how? I've tried TO_DATE() with invalid column name errors...