April 30, 2013 at 11:22 am
I have a table w/a varchar(10) column (MoYear) which has the month and year with '/' between (i.e. 01/2013). My task is to insert '01' as the date surrounded by '/' (i.e. 01/01/2013). Finally I need to convert it to datetime (i.e. 2013-01-01 00:00:00.000) but I keep getting result 'Jan 1 2013 12:00AM'.
I've tried different convert options but still wrong result. So here's my workaround:
1 - ALTER MoYear field to varchar(30)
2 - INSERT the day of the month (01) into field by using UPDATE as:
UPDATE Table
Set MoYear = Substring(MoYear,1,2) + '/01/' + Substring(MoYear,4,4)
3 - Update field MoYear as datetime as:
UPDATE Table
SET MoYear = convert(datetime,convert(varchar,MoYear,101))
So, as a workaround I had to add a datetime field (MoYear2 = cast(null as datetime)) to the table. Then I updated this field with the converted value from field MoYear as:
UPDATE Table
Set MoYear2 = convert(datetime,convert(varchar,MoYear,101))
Then I get the desired result 2013-01-01 00:00:00.000
My question is, am I taking too many steps in achieving the desired result, or is this the best option?
Regards,
John
April 30, 2013 at 11:31 am
You really are doing too much. You can create a computed column using the convert part shown below, just replace the variable @TestDate with your column name :
declare @TestDate varchar(10) = '01/2013';
select @TestDate, convert(datetime, stuff(@TestDate,3,0,'/01'), 101);
April 30, 2013 at 12:20 pm
I just tried your suggestion as:
update table
set MoYear = convert(datetime, stuff(MoYear,3,0,'/01'), 101)
When I select the table the values are displaying as Dec 1 2012 12:00AM; Aug 1 2012 12:00AM, etc.
April 30, 2013 at 12:25 pm
latingntlman (4/30/2013)
I just tried your suggestion as:update table
set MoYear = convert(datetime, stuff(MoYear,3,0,'/01'), 101)
When I select the table the values are displaying as Dec 1 2012 12:00AM; Aug 1 2012 12:00AM, etc.
No, you are trying to update a varchar column when I said you could add a computed column to your table using the convert statement to convert the value in MoYear to a datetime value.
April 30, 2013 at 12:28 pm
If you run the following, you will see that the datetime value is implicitly converted to the character string you showed:
declare @TestDate varchar(10) = '01/2013';
select
@TestDate,
stuff(@testDate,3,0,'/01'),
convert(datetime, stuff(@testDate,3,0,'/01'), 101),
cast(convert(datetime, stuff(@testDate,3,0,'/01'), 101) as varchar);
April 30, 2013 at 12:30 pm
If you want a character version (or at least close) of the value shown as datetime, look at the last convert in the following:
declare @TestDate varchar(10) = '01/2013';
select
@TestDate,
stuff(@testDate,3,0,'/01'),
convert(datetime, stuff(@testDate,3,0,'/01'), 101),
cast(convert(datetime, stuff(@testDate,3,0,'/01'), 101) as varchar),
convert(varchar(30), convert(datetime, stuff(@testDate,3,0,'/01'), 101), 120);
April 30, 2013 at 12:50 pm
By the way, if you are converting MoYear to a datetime value, you really should store it in a datetime column, not back as a character string.
April 30, 2013 at 1:30 pm
You're right, although, I was hoping for an alternative w/o creating another column. Unfortunately, the data type for the field is varchar(10) and the values are 'mm/yyyy' on the table, so it looks like creating the add'l column as datetime is the safer option.
April 30, 2013 at 10:36 pm
Computed column would be a good option for you.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply