January 12, 2007 at 1:24 pm
In SQL server 2k, I have a date column: dtDate, and a time column: dtTime. I would like to combine those into a single column value. How do I extract just the date value from dtDate and just the time value from dtTime so I can add them together? (newbie question)
Sam
January 12, 2007 at 2:02 pm
What are the datatypes of the 2 columns ?
Are they true date/time types (datetime or smalldatetime) or are the char/varchars containing date & time formatted into a string ?
January 12, 2007 at 4:02 pm
They are both smalldatetime data types. The problem is that MS Access is the frontend and unfortunately, the MS Access default if a date is not supplied (user enters a time) is 12/30/1899, which is not compatible with smalldatetime. So I have to capture that and put some other date in the column value.
Sam
January 15, 2007 at 7:56 am
Hi,
problem is that SQL Server does not allow dates prior to year 1900 in smalldatetime, so you have to set the datetype of column dtTime to DATETIME - nor SMALLDATETIME.
You can then calculate the one composite datetime value this way:
DECLARE @dtTime datetime, @dtDate datetime
SET @dtTime = '18991230 12:49:15'
SET @dtDate = '20061024 00:00:00'
SELECT CONVERT(VARCHAR(10), @dtDate, 112) as date_only,
CONVERT(VARCHAR(10), @dtTime, 108) as time_only,
CAST(CONVERT(VARCHAR(10), @dtDate, 112)+' '+CONVERT(VARCHAR(10), @dtTime, 108) as DATETIME) as result
... and insert the Result into the new column. But maybe it would be better to change it in the Access, so that date and time are sent together as one value, not as 2 values? Or do the "capture" and modification of value before it comes to SQL Server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply