separate datetime value to date column, time column

  • Thread starter Thread starter Mitchell_Collen via AccessMonster.com
  • Start date Start date
M

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
 
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.
 
Mitchell_Collen via AccessMonster.com said:
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
Click to expand...
 
can't you just display the same column twice with different input
masks?

Thanks

-Aaron
 
sorry I meant format; not

for the date

mm/dd/yyyy
for the time

hh:nn I believe

-Aaron
 
Back
Top