SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date time Varchar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cats Solutions
Starting Member

6 Posts

Posted - 08/02/2006 :  06:29:35  Show Profile  Visit Cats Solutions's Homepage  Reply with Quote
Hi All,

We are trying to create a new view within SQL 2000. We have two colums from seperate tables, 1 is a datetime data type which stores the Date the other is a varchar that stores the time.

What we would like to do is merge the two into one datetime colum so we can work with this one datetime column. Unfortunaly it is not posible to have this entered into the one field at data entry. If needed we can change the time column from a varchar to a datetime datatype.

Also our SQL knowledge is minimal.

Hope this makes sense any help would be deeply appreciated.

Regards

Darren

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/02/2006 :  08:20:37  Show Profile  Reply with Quote
"What we would like to do is merge the two into one datetime colum so we can work with this one datetime column"
Yes. It is definately easier working with one column of date & time instead of 2 separate column.

"Unfortunaly it is not posible to have this entered into the one field at data entry"
You can combine the date & time from 2 separate data entry during insertion of record into the table.
for example '20060802' + '16:34:34'

"If needed we can change the time column from a varchar to a datetime datatype."
Yes. Change it to datetime data type.

Now on how to combine the 2 field
update yourtable
    set    datetime_col = convert(varchar(8), date_col, 112) + ' ' + time_col


what is the time format like in your time column ?


KH

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2006 :  08:38:42  Show Profile  Reply with Quote
"You can combine the date & time from 2 separate data entry during insertion"

"Now on how to combine the 2 field"

I kept quiet because I only had "complicated" solutions ...

... a VIEW with DATE and TIME columns, and an INSTEAD OF trigger on it ...

... or a Trigger on the Table that did the combination work (and maybe then cleared the "working storage" TIME column). But that requires a dummy column ...

How about putting the TIME in a DateTime datatype column, and then just adding it?

DECLARE @Temp TABLE
(
   MyDate datetime,
   MyTime datetime
)

INSERT INTO @Temp
SELECT '20060802', '13:34:56.790'

-- Review
SELECT * FROM @Temp

-- Process
SELECT MyDate + MyTime
FROM @Temp

-- Combine, if required!
UPDATE U
SET MyDate = MyDate + MyTime,
    MyTime = NULL
FROM @Temp AS U
WHERE MyTime IS NOT NULL

-- Re-Review
SELECT * FROM @Temp

Kristen
Go to Top of Page

Cats Solutions
Starting Member

6 Posts

Posted - 08/03/2006 :  06:54:42  Show Profile  Visit Cats Solutions's Homepage  Reply with Quote
Hi there thanks for your responses, It does not look like we can convert the time colume to DateTime format. Data for the time is being entered as HH:MM will these still work??
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/03/2006 :  07:11:24  Show Profile  Reply with Quote
"Data for the time is being entered as HH:MM will these still work"

Yes, should be fine.

Kristen
Go to Top of Page

Cats Solutions
Starting Member

6 Posts

Posted - 08/03/2006 :  08:06:54  Show Profile  Visit Cats Solutions's Homepage  Reply with Quote
Thats great thanks the only problem that we can for see is if any user inputs the time as HH.MM instead of HH:MM is there any way we can get SQL to replace the . with a : on the fly so to speak??? I know we can repare the database using find and replace in Access (as we know little about SQL :) )
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/03/2006 :  08:12:23  Show Profile  Reply with Quote
REPLACE(MyTime, '.', ':') will make the change "on the fly" (and leave the original alone if it doesn't have a ".")

If there is a risk that the data may NOT be in a valid format you should safeguard against that, otherwise SQL Server will just raise an error ...

SET MyDate = MyDate 
           + CASE WHEN MyTime like '[0-9][0-9][.:][0-9][0-9]'
                  THEN REPLACE(MyTime, '.', ':')
                  ELSE '00:00:00'
                  END

will make the change from "." to ":" and only process times which is digit-digit-separator-digit-digit - otherwise it will leave the date alone (i.e. add 00:00:00 time to it)

Kristen
Go to Top of Page

Cats Solutions
Starting Member

6 Posts

Posted - 08/03/2006 :  10:04:00  Show Profile  Visit Cats Solutions's Homepage  Reply with Quote
Great almost there!!!! one more question no matter what I set the style ID to for example 120 (yy/mm/dd HH:MM:SS 24hr) I always get dd/mm/yy HH:MM:SS 24hr even if I try any off the other Styles it always returns this format??????????? we need the output to be 120 (yy/mm/dd HH:MM:SS 24hr)

Using

Select convert(datetime,convert(char(8),TheDateColumn,112)+' '+TheTimeColumn) as TheDatetime
from TheDateTable
Inner Join TheTimeTable
on TheDateTable.joincolumn = TheTimeTable.joincolumn

We then set up another view looking at the above view

select convert(datetime, prevView, 120) as datetime

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 08/03/2006 :  10:22:44  Show Profile  Reply with Quote
if you need the datetime in format YY/MM/DD ..... you should convert the datetime to varchar. I assume preView column is a datetime data type ?

select convert(varchar(30), prevView, 120)



KH

Go to Top of Page

Cats Solutions
Starting Member

6 Posts

Posted - 08/03/2006 :  10:59:53  Show Profile  Visit Cats Solutions's Homepage  Reply with Quote
Thanks All for your help we now have the solution. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000