September 17, 2008 at 2:02 am
I have to check if the timepart of a datetime attribute is filled or not, I notice that some dates are inserted only with the datepart. If timepart is not there then the value '00:00:000' needs to be used.
Now, I can think up something with CASTing the datetime to a VARCHAR(15) and then use LEN to decide if the timepart is there; I wonder however if there is maybe a simple way to conclude the timepart is filled.
This IS about SQL2K. Any ideas?
Greetz,
Hans Brouwer
September 17, 2008 at 2:37 am
HI there,
I think this might help.
[font="Courier New"]
DECLARE @dte DATETIME
SET @dte = '2008-01-02 00:00.000'
SELECT
@dte AS [StartDAte],
DATEADD(dd, DATEDIFF(dd,0, @dte),-1) AS [Day before with no time],
DATEDIFF(ms,DATEADD(dd, DATEDIFF(dd,0, @dte),-1),@dte) [Diff in MilliSeconds],
CASE WHEN DATEDIFF(ms,DATEADD(dd, DATEDIFF(dd,0, @dte),-1),@dte) != 86400000
THEN 'HAS TIME'
ELSE 'HAS NO TIME'
END AS [Solution][/font]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 17, 2008 at 2:46 am
Hi,
Why don't you use the CONVERT function?
Following code should return the time in format hh:mm:ss in @time
declare @time char(10)
select @time = convert(char(10), @your_date, 8)
September 17, 2008 at 2:54 am
Here: DateOnly function
you may find UDF dbo.DateOnly.
If @DateTime = dbo.DateOnly(@DateTime) then there is no time portion.
_____________
Code for TallyGenerator
September 17, 2008 at 3:03 am
Tnx for answering, Chris. It took me awhile to figure out the SET @dte should be without the timepart, or with another value then midnight. Far more elegant then I had in mind.
Tnx again.
Greetz,
Hans Brouwer
September 17, 2008 at 3:06 am
HI,
My set date was an example but if you date has a time then the SET @dte = '2008-01-01 01:01:00.010'
or whatever your data is.
looking at the other solutions mine might be long winded he he he
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply