Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 build datetime out of time and date portions
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

236 Posts

Posted - 02/14/2013 :  12:15:59  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/14/2013 :  12:40:48  Show Profile  Reply with Quote
Here is one way:
--Set up sample data

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

--Select values as DATETIME
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/14/2013 :  12:43:25  Show Profile  Reply with Quote
The issue with this approach is that the user is free to enter anything - a date in a format other than, 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 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:
	[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')

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

Go to Top of Page

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/14/2013 :  12:44:59  Show Profile  Reply with Quote
Forgot to add, here is a link to MSDN that describes the different SYTLEs that can be used with the CONVERT fucntion:

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

Very Important crosS Applying yaK Herder

52326 Posts

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


SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000