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
 build datetime out of time and date portions		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
161 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

Lamprey
Flowing Fount of Yak Knowledge

3821 Posts
Posted - 02/14/2013 :  12:40:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1483 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 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
Flowing Fount of Yak Knowledge

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

India
47023 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

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
  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.06 seconds. Powered By: Snitz Forums 2000