June 22, 2011 at 5:20 am
Hi
I'm trying to format the column(dueDate) which contains the date to a YYYYMMDD format
My understanding to achieve this would be
select convert(varchar,getdate(),112)
select CONVERT(date,duedate,112)
from test
--my attempt
select convert(varchar,duedate,112
from test
--Test table
create table test
(
[duedate][varchar](10) not null
)
--inserting data
insert into test
values('010711')
insert into test
values('090611')
insert into test
values('100611')
insert into test
values('140911')
insert into test
values('150911')
However, my attempt is not returning the desired results, can someone tell me where I'm going wrong here
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 22, 2011 at 5:30 am
Jnrstevej (6/22/2011)
HiI'm trying to format the column(dueDate) which contains the date to a YYYYMMDD format
My understanding to achieve this would be
select convert(varchar,getdate(),112)
select CONVERT(date,duedate,112)
from test
--my attempt
select convert(varchar,duedate,112
from test
--Test table
create table test
(
[duedate][varchar](10) not null
)
--inserting data
insert into test
values('010711')
insert into test
values('090611')
insert into test
values('100611')
insert into test
values('140911')
insert into test
values('150911')
However, my attempt is not returning the desired results, can someone tell me where I'm going wrong here
Your problem is that the entries in your test table are not dates. Make them dates and your syntax works (though I would probably use Varchar(8)).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 22, 2011 at 5:34 am
Try something like this:
DECLARE @a VARCHAR(20) = '010711'
SELECT @a,
'20' + RIGHT(@a, 2) + SUBSTRING(@a, 3, 2) + LEFT(@a, 2)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 23, 2011 at 5:11 am
I've tried your method of changing the data type in the creation of the table, it returned the results i desired. Thanks for your advice
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 27, 2011 at 1:50 pm
Yes, as others have pointed out, your column in your table is not a datetime data-type.
However, once you get past that hurdle, and if you want to return a character in YYYYMMDD without separators, this code will work:
select replace(convert(char(10), getdate(), 120), '-', '')
OUTPUT: 20110627
If you don't care about the separators, then unwrap the REPLACE function thusly:
select convert(char(10), getdate(), 120)
OUTPUT: 2011-06-27
Good luck!
June 27, 2011 at 3:14 pm
repicurus (6/27/2011)
select replace(convert(char(10), getdate(), 120), '-', '')
How is this an improvement over the code listed below that the OP is already using?
select convert(varchar,getdate(),112)
I can't imagine that the efficiency of converting to format 120 is so much superior to converting to format 112 that it more than makes up for the additional cost of the replace function.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 27, 2011 at 8:10 pm
In addition to what others have pointed out - you should always specify the length of the data type when casting or converting. Using this:
SELECT convert(varchar, duedate, 112) FROM test;
Should be:
SELECT convert(char(8), duedate, 112) FROM test;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply