making a date field read as MMDDYYYY

Hello:
I am trying to get a field that reads as 6/29/1983 as 06291983, including a
leading zero where any month fields are not double digit months. It needs t
o
read as MMDDYYYY.
Is there a way to do this? I tried the following statement but for some
reason it returned only a bogus year and not even the month, day, or even th
e
correct year.
Please help!
select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
'+DATEPART(yyyy,BRTHDATE) from UPR00100Here's one method (assuming datatype is datetime):
SELECT REPLACE(CONVERT(varchar(20), GETDATE(), 105), '-', '')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
a
> leading zero where any month fields are not double digit months. It needs
to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||select replace(convert(varchar(10), getdate(), 101), '/', '')
Your Query doesn't work because the Datepart function returns Integers.
Even if you Cast them as Varchar, leading zeros would be dropped.
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
> a
> leading zero where any month fields are not double digit months. It needs
> to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
> the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||DATEPART returns an int, so you need to convert it to char or varchar to
create the string.
"childothe1980s" wrote:

> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
a
> leading zero where any month fields are not double digit months. It needs
to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||Come from VBScript? I sympathize. In T-SQL, you can't just add integers to
strings and expect SQL Server to know what you are trying to do... T-SQL is
slightly more strongly typed than VBScript.
Give the following a try instead:
SELECT RIGHT(d,4) + LEFT(d, 4) FROM
(SELECT d = CONVERT(CHAR(8), GETDATE(), 112)) x;
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
> Hello:
> I am trying to get a field that reads as 6/29/1983 as 06291983, including
> a
> leading zero where any month fields are not double digit months. It needs
> to
> read as MMDDYYYY.
> Is there a way to do this? I tried the following statement but for some
> reason it returned only a bogus year and not even the month, day, or even
> the
> correct year.
> Please help!
> select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
> '+DATEPART(yyyy,BRTHDATE) from UPR00100|||Try:
select
replace (convert (varchar (20), getdate (), 101), '/', '')
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:6F61E799-2348-42BD-8C6F-0CC04DE54624@.microsoft.com...
Hello:
I am trying to get a field that reads as 6/29/1983 as 06291983, including a
leading zero where any month fields are not double digit months. It needs
to
read as MMDDYYYY.
Is there a way to do this? I tried the following statement but for some
reason it returned only a bogus year and not even the month, day, or even
the
correct year.
Please help!
select DATEPART(mm,BRTHDATE)+' '+DATEPART(dd,BRTHDATE)+'
'+DATEPART(yyyy,BRTHDATE) from UPR00100