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
 build datetime out of time and date portions

Author  Topic 

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-02-14 : 12:15:59
I have a Time and a Date colum that are both imported from an Access Database. Time is of TIME type, while Date had been defined as NVARCHAR. In order to be able to use the SQL time functions I want to build a computed column that returns a value of DATETIME type.
  Date         Time                    Getdate()
------------------------------------------------------
15.02.12 18:07:33.0000000 2013-02-14 15:52:27.027


SELECT CAST('20'+right(DATE,2)+'-'+RIGHT(left(DATE,5),2)+'-'+LEFT(DATE,2)+' ' + left(cast(TIME as NVARCHAR),8) as DateTime)works perfectly; but then....

I had been trying this for the last 3 hours and its driving me completely nuts: while the result the query returns in a select command is ok, once inserted in the Calculated Column Specification the seconds get swallowed.

This, because the computed column turns Cast(time as NVARCHAR) into CONVERT(NVARCHAR, time,0) and returns 6:07PM, while it doesn't if used in select... then converting it back to DATETIME the seconds are gone.

This is so confusing and it takes me to all these different regional related standarts and formats.
I tried to specify the european format CONVERT(NVARCHAR, time,25) but then all of the sudden, the hole formats I built got messed up.

Any comment?
Regards, Martin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-14 : 12:40:48
Here is one way:
--Set up sample data
DECLARE @Date NVARCHAR(25);
DECLARE @Time TIME;

SET @Date = '15.02.12'
SET @Time = CAST('18:07:33.0000000' AS TIME)

--Select values as DATETIME
SELECT CONVERT(DATETIME, @Date, 4) + CAST(@Time AS DATETIME)
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 12:43:25
The issue with this approach is that the user is free to enter anything - a date in a format other than dd.mm.yy, or even any other random string into the Date column. They will be able to successfully enter it, but when you query it, the query will fail. This is the perennial problem with storing dates and times as character strings.

If you can ensure that the data in the Date column will always conform to the dd.mm.yy format, then you should be able to succesfully create a computed column and use it. The formula for the computed column can be simpler as shown below:
CREATE TABLE #tmp 
(
[Date] NVARCHAR(50),
[time] TIME,
DateAndTime AS CONVERT(DATETIME,[Date],4) +[time]
)

-- this should work correctly.
INSERT INTO #tmp VALUES ('15.02.12','18:07:33.0000000')
SELECT * FROM #tmp;

-- this insert will succeed
INSERT INTO #tmp VALUES ('blabla','18:07:33.0000000');
-- but the select will fail.
SELECT * FROM #tmp;

DROP TABLE #tmp;
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-14 : 12:44:59
Forgot to add, here is a link to MSDN that describes the different SYTLEs that can be used with the CONVERT fucntion:
http://technet.microsoft.com/en-us/library/ms187928.aspx

If you DATE column has more than more format then things get more difficult.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 23:12:44
best thing would be do front end validations and convert all dates to a unified format (preferably unambiguos iso format) before sending to DB

see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -