Date time Varchar - 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

General SQL Server Forums

New to SQL Server Programming

Date time Varchar

Author

Topic

Cats Solutions
Starting Member

6 Posts

Posted - 2006-08-02 : 06:29:35

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)

17689 Posts

Posted - 2006-08-02 : 08:20:37

"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 ?

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 08:38:42

"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

Cats Solutions
Starting Member

6 Posts

Posted - 2006-08-03 : 06:54:42

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??

Kristen
Test

22859 Posts

Posted - 2006-08-03 : 07:11:24

"Data for the time is being entered as HH:MM will these still work"

Yes, should be fine.

Kristen

Cats Solutions
Starting Member

6 Posts

Posted - 2006-08-03 : 08:06:54

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 :) )

Kristen
Test

22859 Posts

Posted - 2006-08-03 : 08:12:23

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

Cats Solutions
Starting Member

6 Posts

Posted - 2006-08-03 : 10:04:00

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-03 : 10:22:44

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)

Cats Solutions
Starting Member

6 Posts

Posted - 2006-08-03 : 10:59:53

Thanks All for your help we now have the solution. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources