separate datetime value to date column, time column

[Mitchell_Collen via AccessMonster.com]

Hi,
Will you help me format my datetime date to separate columns. One column for
time, and the other for date. I have used the MSDN site's transact-sql
documents but am still able to produce results.

Here is a typical date value: 06/16/2007 16:17:38

I have tried these:

SELECT CONVERT(datetime, TxDatetime, 126) AS Expr1, CONVERT(datetime,
TxDatetime, 102) AS Expr2, CAST(TxDatetime AS datetime) AS Expr3, DATEPART
(hhmmss, TxDatetime) AS Expr4
FROM dbo.phmPYXHx

I want it to produce this:

expr1 expr2
06/16/2007 16:17:38

Thanks in advance.
Misty

 

[Sylvain Lafontaine]

From http://msdn2.microsoft.com/en-us/library/ms187928.aspx , we can see
that we must convert a datetime value to a varchar(10) and varchar(8)
respectively using the convert function with a format:

select convert (varchar(10), getdate(), 101), convert (varchar(8),
getdate(), 108)

If you convert these two values back to a datetime, the date will have a
value of midnight for its time part and the time will have a value of
01/01/1900 for its date part. On Access, the starting point for the date
(or put another way, the date value for a time without a date) is
30/12/1899, not 01/01/1900; so there is a difference of two days. This
difference is important if you want a text control on Access to display a
datetime value as only a time without a date. Of course, you don't have
this problem if you keep your values as varchar(10) and varchar(8)
respectively.

 

[chandrasekaran]

Hi,
Will you help me format my datetime date to separate columns. One column for
time, and the other for date. I have used the MSDN site's transact-sql
documents but am still able to produce results.

Here is a typical date value: 06/16/2007 16:17:38

I have tried these:

SELECT CONVERT(datetime, TxDatetime, 126) AS Expr1, CONVERT(datetime,
TxDatetime, 102) AS Expr2, CAST(TxDatetime AS datetime) AS Expr3, DATEPART
(hhmmss, TxDatetime) AS Expr4
FROM dbo.phmPYXHx

I want it to produce this:

expr1 expr2
06/16/2007 16:17:38

Thanks in advance.
Misty

 

[aaron.kempf]

can't you just display the same column twice with different input
masks?

Thanks

-Aaron

 

[aaron.kempf]

sorry I meant format; not

for the date

mm/dd/yyyy
for the time

hh:nn I believe

-Aaron