Set time to default value as zero

Question

Post reply

Set time to default value as zero

satishp.mst

Grasshopper

Points: 14
More actions
July 17, 2009 at 11:27 pm

#212332

I am having a stored procedure that inserts data in a table. There is a date column in which I am taking the time using getdate() function and inserting the time in that date column. It saves date and time in that column.
For e.g. 17/07/2009 14:23:10
What I want is to store date and time, but time should always default to zero.
For e.g. 17/07/2009 00:00:00
Please tell me how to do this.

Viewing 4 posts - 1 through 4 (of 4 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

satishp.mst (7/17/2009)

What I want is to store date and time, but time should always default to zero.

For e.g. 17/07/2009 00:00:00

quote]

Hi,

only option you must convert the datetime and insert it to the table

like

create table #temp

(

slno int,

date1 datetime

)

insert into #temp

select 1,getdate()

union all

select 2,convert(varchar(15),getdate(),101)

or

Table like

create table #temp_TABLE

(

slno int,

date1 datetime,

Dayonly as cast(convert(varchar(15),date1,101)as datetime)

)

insert into #temp_TABLE

select 1,getdate()

LutzM SSC Guru Points: 107049 More actions · Answer 2

Based on arun's sample data there's another option (which is usually faster):

create table #temp

(

slno int,

date1 datetime

)

insert into #temp

select 1,getdate()

union all

select 2,DATEADD(dd,0,DATEDIFF(dd,0,getdate()))

For a performance comparison of the various date conversion functions please see one of Gails articles [/url].

For a list of several date functions please see one of Lynn's blogs.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Let me come right out and say it... don't use CONVERT to strip the time... it's at least ten times slower than just about any other method.

So far as testing goes, here's a bit of testing I did a while back...

First, my standard million row test table...

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 100,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

And, now the test code which does the conversions into a "bit bucket" variable to take the display out of the picture...

DECLARE @BitBucketDATETIME DATETIME

PRINT '========== BASELINE =========='

SET STATISTICS TIME ON

SELECT @BitBucketDATETIME = SomeDate