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.
| 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 dataDECLARE @Date NVARCHAR(25);DECLARE @Time TIME;SET @Date = '15.02.12'SET @Time = CAST('18:07:33.0000000' AS TIME)--Select values as DATETIMESELECT CONVERT(DATETIME, @Date, 4) + CAST(@Time AS DATETIME) |
 |
|
|
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 succeedINSERT INTO #tmp VALUES ('blabla','18:07:33.0000000');-- but the select will fail.SELECT * FROM #tmp;DROP TABLE #tmp; |
|
|
|
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.aspxIf you DATE column has more than more format then things get more difficult. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
build datetime out of time and date portions