Only want to insert Date part of datetime variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Only want to insert Date part of datetime variable

Author

Topic

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-01 : 12:08:14

Hi

I am inserting a datetime variable into a table i.e. @rosterdate and only want to insert the date part, but I can't figure out how to remove the time.

Please help

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-01 : 12:17:28

convert(varchar(8), @dte, 112)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 12:25:26

Depends on what the table id defined as....got DDL?


USE Northwind
GO

CREATE TABLE myTable99(Col1 datetime, Col2 varchar(8))
GO

DECLARE @dte datetime
SELECT @dte = Getdate()
SELECT convert(varchar(8), @dte, 112)

INSERT INTO myTable99(Col1, col2)
SELECT convert(varchar(8), @dte, 112), convert(varchar(8), @dte, 112)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-01 : 17:51:16

Thanks

iloveorangesoda
Starting Member

30 Posts

Posted - 2004-09-02 : 08:29:26

Hi

That worked great

Cheers

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-02 : 08:36:02

There is no DATE datatype in SQL Server, only Datetime. What the guys were describing for you is how to strip out the time and leave it as 00:00:00. If you only want the date stored, store it in a VARCHAR(8) column.

Raymond

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 09:39:09

well that is true if you're inserting data from QA.

if you're doing it from .net dataset then you can put only date without the time part.
access to the db was through sproc with paramters.
we added only the date part into the parameter and it was handeled
by itself. it didn't insert the time part.

Go with the flow & have fun! Else fight the flow :)

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-02 : 09:41:09

Regardless of what you use, if the underlying column datatype is datetime you will get the 00:00:00 time portion.

Raymond

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 09:44:52

ok true, but why does the EM show only the date portion????

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-02 : 09:46:26

quote:
Originally posted by raymondpeacock

Regardless of what you use, if the underlying column datatype is datetime you will get the 00:00:00 time portion.

Raymond

Not unless you you use varchar...which I would suggest is a bad idea...

Or you could wait and use yukon....

Brett

8-)

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-02 : 09:49:35

Like any other front-end to the database, it can choose to format the returned result set I guess. If the time portion is 00:00:00 EM seems to default to not show it. Kind of makes sense to do this.

Raymond

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 09:51:30

ok true, my bad. i still don't take EM as a front end, who knows why...

Go with the flow & have fun! Else fight the flow :)

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-02 : 09:51:36

Brett - I said you'd get the time portion only if you use Datetime data type. So if it was varchar then obviously you wouldn't get the 00:00:00!

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources