Operation on DateTime

Hi there
Presently I have a table with a DateTime column as the following:
Col1
2003/12/01 00:05:00
2003/12/01 00:10:00
2003/12/01 00:15:00
2003/12/01 00:20:00
What I want to do is to separate the date and time into two columns,
so it becomes:
Col1 Col2 Col3
2003/12/01 00:05:00 2003/12/01 00:05:00
2003/12/01 00:10:00 2003/12/01 00:10:00
2003/12/01 00:15:00 2003/12/01 00:15:00
2003/12/01 00:20:00 2003/12/01 00:20:00
.
.
..
I am wondering which way is easier - making a program to do this (e.g. in
C#)
or using SQL queries. Thanks for any suggestions.
YuelinDepends where you need the values, you can use the RIGHT Function for that,
just use aa format that also represents the time format and Cut of the 8
right Characters from it, therell you be.
Or you go by:
Select Getdate(),
Convert(varchar(10),getdate(),111),Conve
rt(varchar(10),getdate(),114)
But IMHO formatting should be a client work.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>|||Hi
You can write the query this way
UPDATE <TABLE>
SET
col2 = convert(varchar(10), col1,102),
col3 = convert(varchar(8), col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||update <table>
set
col2 = convert(varchar(10),col1,111),
col3 = convert(varchar(10),col1,108)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> ..
> ..
> ...
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>
>|||Thanks for the advice.
The original table I have doesn't have the column 2 and column 3, I need to
populate the two columns based on the datetime data in the column 1.
How to do this in T-SQL?
Thanks
Yuelin
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
> update <table>
> set
> col2 = convert(varchar(10),col1,111),
> col3 = convert(varchar(10),col1,108)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Yuelin Liang" wrote:
>|||Then you either have to extend the table before updating then table, or you
insert the new data in a new table via:
Select
col1,
convert(varchar(10),col1,111) as col2,
convert(varchar(10),col1,108) as col3
Into NewTable
From Yourtable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Yuelin Liang" <yuelinliang@.hotmail.com> schrieb im Newsbeitrag
news:OYPTy0sWFHA.1508@.tk2msftngp13.phx.gbl...
> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need
> to
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>|||Hi,
Then u can use this way
SELECT col1, convert(varchar(10),col1,111) col2,
convert(varchar(10),col1,108) col3
FROM <TABLE>
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Yuelin Liang" wrote:

> Thanks for the advice.
> The original table I have doesn't have the column 2 and column 3, I need t
o
> populate the two columns based on the datetime data in the column 1.
> How to do this in T-SQL?
> Thanks
> Yuelin
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:24C0B6EE-354B-41EA-9AD6-7CB11AC3B52C@.microsoft.com...
>
>|||The problem with changing the structure of a physical table to meet some
presentation requirement is that different applications or users have
different requirements. For example, what if one user wants YYYY/MM/DD and
another wants MM/DD/YYYY? Also, the transformed representation of a value
is ususally not as storage efficient as the native data format. Rather than
modify your existing physical table, leave the date as datetime in the table
and implement the transformations as Views. The convert function can be used
to represent datetime as a string in various formats.
"Yuelin Liang" <yuelinliang@.hotmail.com> wrote in message
news:uacpnCsWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi there
> Presently I have a table with a DateTime column as the following:
> Col1
> 2003/12/01 00:05:00
> 2003/12/01 00:10:00
> 2003/12/01 00:15:00
> 2003/12/01 00:20:00
>
> What I want to do is to separate the date and time into two columns,
> so it becomes:
> Col1 Col2 Col3
> 2003/12/01 00:05:00 2003/12/01 00:05:00
> 2003/12/01 00:10:00 2003/12/01 00:10:00
> 2003/12/01 00:15:00 2003/12/01 00:15:00
> 2003/12/01 00:20:00 2003/12/01 00:20:00
> .
> .
> ..
> I am wondering which way is easier - making a program to do this (e.g. in
> C#)
> or using SQL queries. Thanks for any suggestions.
> Yuelin
>