Convert date into two fields - SQL Server Forums

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

SQL Server Development (2000)

Convert date into two fields

Author

Topic

AFK
Starting Member

26 Posts

Posted - 2005-03-21 : 10:12:51

I have a smalldatetime value in original table that looks in table like

DateTime 2005-03-21 10:07:00

I have a temp table with two fields

Date smalldatetime
Time smalldatetime

I want to insert Datetime in these two fields. How do I seperate the date and time value to get two values seperately date and time.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-21 : 10:24:41

You don't want to do this....

SELECT CONVERT(varchar(25),GetDate(),101), CONVERT(varchar(25),GetDate(),108)

Brett

8-)

AFK
Starting Member

26 Posts

Posted - 2005-03-21 : 10:36:12

I use this like
Declare Date smalldatetime,
Time smalldatetime
Select
CONVERT(varchar(25),DateTime,101) 'Date',
CONVERT(varchar(25),DateTime,108) 'Time'
From Table

and it bring back the result like

Date
2005-03-01 00:00:00
Time
1900-01-01 10:03:00

Is it possible to get only date like2005-03-01 and time like 10:03:00.

Because in original table its only one field, but in my temp table it has to go to two different fields. Thanks for helping.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-21 : 11:04:19

This may help you out:

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-21 : 12:16:15

Well there is that for sure...

But if it's 2 columns what's their datatypes. What was the original Source? DB2

And it looks like M$ bailed on have a date and time datatype in 2005.

Brett

8-)

AFK
Starting Member

26 Posts

Posted - 2005-03-30 : 10:55:54

Actually in original table its one column
and the datatype is smalldatetime
DateTime 2005-03-21 10:07:00 (smalldatetime)

but the table I am creating two columns
Date (smalldatetime)
Time (smalldatetime).

I am still trying...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-31 : 21:50:13

AFK,
Converting the datetime to Date and Time makes it two varchar strings:

CONVERT(varchar(25),GetDate(),101), CONVERT(varchar(25),GetDate(),108)

You need to set the columns in your temp table as varchar instead of datetime.more importantly, as X002548 says,
"You don't want to do this..."

There's never enough time to type code right,
but always enough time for a hotfix...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 22:01:49

Did you read my blog? I explain exactly how to separate the date from the time with examples and everything. I even provide a trigger that does it automatically. There's no need for CONVERT() functions or anything, it's pretty simple stuff.

- Jeff

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-01 : 13:43:28

I read it, Jeff!
That is good programming advice

, and actually it is what I read and then converted everything on this DB I inherited here not too long ago. My response to AFK was just to address his problem; Your blog solution is far better...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources