A SQL Server 2005 db has three date related-columns (MonthGiven,DayGiven, YearGiven) each as smallint datatype. I would like tocreate a DocDate column (datetime datatype) that combines the data fromthe three existing date-related columns. I have tried casting andsimple concatentation without success.
ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting buthave been unable to implement info I have found in the SQL ServerDeveloper Center in my situation.
I think this should work, it works for me
UPDATE Details SET DocDate = CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)
You can also use
UPDATE Details SET DocDate = CONVERT(DateTime, CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar), 101)
The last parameter (style) will change based on the input string to convert
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
|||
Thanks for your prompt suggestions. I tried both and got the same error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Sinceboth produced the same error, I must assume that either Imisrepresented my data or there is something within the data itselfthat is producing the error. I rechecked the datatypes, etc of myoriginal post and think that is ok.
I queried the data: SELECT MonthGiven, DayGiven, YearGiven FROM Details
When a month, day, or year is unknown the data value = 0
Iam guessing that is causing the error. If so, should i replacethose values with NULL or a string of 0s (Month = 00, Day = 00, Year =0000)?
Thanks again for your input and any further assistance you can provide.
|||
To handle the null values, you can add a where clause to the update statement (add extra conditions in case other illegal values are expected)
Update ...
where MonthGiven is not null and DayGiven is not null and YearGiven is not null
Prashant,
Here is my TSQL:
ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CONVERT(DateTime, CAST(MonthGiven ASVarchar)+ '/' + CAST(DayGiven AS Varchar) + '/' + Cast(YearGivenAs Varchar), 101) WHERE MonthGiven is not null and DayGiven isnot null and YearGiven is not null
Here is the error:
Conversion failed when converting datetime from character string.
|||
vish4forum:
I think this should work, it works for me
UPDATE Details SET DocDate = CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)
I think you can modify your table this way:
decare @.time varchar(100)
set @.time=CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)
update details set docdate=convert(datetime,@.time)
This should work well.
Hope my suggestion helps
|||Hi B.C.,
I corrected spelling of declare and ran your query with this result:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'MonthGiven'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'DayGiven'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'YearGiven'.
|||
I am trying another approach which may simplify this problem.
ALTER TABLE Details ADD DocDate varchar(20) NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS VARCHAR(5) )+ '/' +CAST(DayGiven AS VARCHAR(5)) + '/' + CAST(YearGiven AS VARCHAR(5))
Sonow I have a DocDate column in the Details table with varchar data(e.g., 9/12/2007)) that simply needs to be cast as datetime. Howcan I do that?
|||
This query executed successfully, but in Object Explorer the data incolumn DocDate still shows as VarChar(20) after refreshing thetable.
UPDATE Details SET DocDate=CONVERT(Datetime,101)
Iwas expecting it to be datetime. Any thoughts on why the queryexecuted successfully but the datatype did not change in ObjectExplorer?
|||
Hi moonshadow,
My fault. The solution i gave above is wrong. You cannot use a intermedia variable in this case.
You can try the suggestionPrashant Kumar provided above. That should work. And as to your question, "UPDATE Details SET DocDate=CONVERT(Datetime,101)
I was expecting it to be datetime " , you cannot do that-- the string which you want to convert to datetime must follow a certain format, for example, 9/12/2007 or 2007-9-12. Based on my understanding, i think convert 101 to datetime value dosn't make sense.The right format is : convert(datetime, '9/12/2007') after which you will get a datetime value.
I would suggest you reading some materials on sql datetime. You will find that's very helpful to solve your problem. thanks
|||
Thanks to all for your patience and very helpful advice.
