User Defined Data Types

Question

Post reply

User Defined Data Types

sasheen

SSC Veteran

Points: 289
More actions
January 8, 2008 at 10:49 pm

#177908

Hi,
I am attempting to create a data type based on datetime that will have the date portion set to 1900/01/01. I have done this via a CREATE DEFAULT, sp_addtype. Well, sort of. Of course that only does it when no date is specified. I still need it set to 1900/01/01 even when a date is specified.
Example
INSERT INTO DataTypesTest
(MyDate)
Values
('Jun 6 2003 05:01:23')
Would result in this being saved to the table
1900-01-01 05:01:23.000
Instead of
2003-06-06 05:01:23.000
Suggestions / Advice?
Thank you.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

What is datatype you have created?

Check the following example where in i am getting correct insert

Create table #1(d1 datetime DEFAULT getdate() )

insert into #1 values('')

insert into #1 values('2008-01-09')

select * from #1

sasheen SSC Veteran Points: 289 More actions · Answer 2

I am attempting to have a column where there is no date portion. Only a time portion. Your example will keep the date portion specified. That needs to be stripped and only the time portion left.

This is how is how I started. This code will strip the date portion of the default. Leaving just the time.

CREATE DEFAULT dtTimeDefault

AS cast(cast(getdate() AS float) - floor(cast(getdate() AS float)) AS DateTime )

This works fine when the date is not specified. I need this to work when the date is specified as well.

Suggestions?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You cannot do that in a default, because column values are not available in a default.

Just do the conversion when you insert the data. Or don't bother creating a column with the time only; just do in in a view or a computed column.

The code below it probably to best way to do the time-only conversion:

select

a.DT,

TIME_ONLY = a.DT-dateadd(dd,datediff(dd,0,a.DT),0)

from

(

--Test Data

select DT = getdate() union all

select DT = '20061231 23:59:59.997'

) a

Results:

DT TIME_ONLY

----------------------- -----------------------

2008-01-09 10:49:35.390 1900-01-01 10:49:35.390

2006-12-31 23:59:59.997 1900-01-01 23:59:59.997

(2 row(s) affected)

sasheen SSC Veteran Points: 289 More actions · Answer 4

I need the conversion to happen automatically, with out any action by the user or applications using the DB. There are multple apps that access the DB. Some play nice, some do not. Do not want to prevent the entry if the date is specified, just have it stripped out.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 5

Then it seems you have to use triggers.

...and your only reply is slàinte mhath

sasheen SSC Veteran Points: 289 More actions · Answer 6

Possible to do one trigger on the whole DB to be fired for a specific data type? Otherwise a trigger is not pratical as there are over 500 columns effected.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You'll have to write a trigger for each table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 8

Yet another option is to use views to output data in desired format. In any way, idea of stripping date part in 500 columns is work costly.

...and your only reply is slàinte mhath

sasheen SSC Veteran Points: 289 More actions · Answer 9

Triggers on INSERT and UPDATE are the 'back up' if I can not come up with a better way.