June 18, 2008 at 3:59 pm
Anyone create a user defined data type similiar to the Date data type in 2008?
I have attempted to do so, but have one issue. Based on a datetime type, even if the time is specified I want the time to be zeroed out. Currently, it is only zeroed when the time is not specified.
Example:
INSERT INTO DataTypesTest
(MyDate, MyTime)
Values
('Jun 6 2003 09:01:23','14:11:03')
I want to return this to be saved to the table.
Jun 6 2003 00:00:00
Not this, which is what is saved.
Jun 6 2003 09:01:23
I bind this default to my data type
CREATE DEFAULT dtDateDefault
AS cast(datediff(dd,0,getdate()) AS DateTime)
Any suggestions/comments/better ideas (other than moving to 2008 :D)?
Thanks
June 18, 2008 at 4:52 pm
I don't really see the point in what you are doing, because you can just break out the date and time parts when you need to.
You can break them out like this:
select
DateOnly = dateadd(dd,datediff(dd,0,getdate()),0),
TimeOnly = getdate()-dateadd(dd,datediff(dd,0,getdate()),0)
Results:
DateOnly TimeOnly
----------------------- -----------------------
2008-06-18 00:00:00.000 1900-01-01 18:55:41.913
June 18, 2008 at 5:41 pm
Thank you. I am aware of that.
June 19, 2008 at 12:15 am
I always just use the Convert function (albeit in SQL2005, but I'm sure it hasn't change) to insert the appropriate date only conversion of the date. I'm sure you could use that on the fly as a calculated field.
e.g.,
SELECT CONVERT (nvarchar(MAX),GetDate(),112)
Returns:
20080619
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply