Convert CHAR YYYYMMDD to mm/dd/yyyy

[rdraider]

I can't seem to find a method of converting a CHAR(8) column in the form of
YYYYMMDD to an actual date such as mm/dd/yyyy

Can anyone point me in the right direction?

Thanks

[rdraider]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

Correction the source field in an INT type
So how to convert INT type YYYYMMDD to a date mm/dd/yyyy


"rdraider" <rdraider@sbcgl obal.netwrote in message
news:7Unzi.1102 8$3x.3766@newss vr25.news.prodi gy.net...

>I can't seem to find a method of converting a CHAR(8) column in the form of
>YYYYMMDD to an actual date such as mm/dd/yyyy
>
Can anyone point me in the right direction?
>
Thanks
>

[David Portas]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

"rdraider" <rdraider@sbcgl obal.netwrote in message
news:7Unzi.1102 8$3x.3766@newss vr25.news.prodi gy.net...

>I can't seem to find a method of converting a CHAR(8) column in the form of
>YYYYMMDD to an actual date such as mm/dd/yyyy
>
Can anyone point me in the right direction?
>
Thanks
>

'YYYYMMDD' is one of the standard, non-regional formats supported by SQL
Server. No special conversion is necessary:

DECLARE @dt CHAR(8);
SET @dt = '20070823';

SELECT CAST(@dt AS DATETIME) AS dt;

dt
------------------------------------------------------
2007-08-23 00:00:00.000

(1 row(s) affected)


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[David Portas]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

"rdraider" <rdraider@sbcgl obal.netwrote in message
news:5_nzi.1102 9$3x.7225@newss vr25.news.prodi gy.net...

Correction the source field in an INT type
So how to convert INT type YYYYMMDD to a date mm/dd/yyyy
>
>

DECLARE @dt INT;
SET @dt = 20070823;

SELECT CAST(CAST(@dt AS CHAR(8)) AS DATETIME) AS dt;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[rdraider]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

Thanks David.

I know how to generate CREATE TABLE scripts but is there a fast way to
generate INSERT statements for the actual data?



"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.orgwrote in message
news:PZudnSBdYI xykVPbnZ2dnUVZ8 qGdnZ2d@giganew s.com...

"rdraider" <rdraider@sbcgl obal.netwrote in message
news:5_nzi.1102 9$3x.7225@newss vr25.news.prodi gy.net...

>Correction the source field in an INT type
>So how to convert INT type YYYYMMDD to a date mm/dd/yyyy
>>
>>

>
>
DECLARE @dt INT;
SET @dt = 20070823;
>
SELECT CAST(CAST(@dt AS CHAR(8)) AS DATETIME) AS dt;
>
--
David Portas, SQL Server MVP
>
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
>
SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--
>
>

[--CELKO--]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

>>'YYYYMMDD' is one of the standard, non-regional formats supported by SQL Server. No special conversion is necessary: <<

Picky, picky, but the proper term is "ISO-8601 Standard" and the
Standard SQL format is "yyyy-mm-dd" from that Standard :)

[rdraider]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

Conversion necessary because the &%#%$&)# user wants to see mm/dd/yyyy

Thanks

"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1187913393 .825664.145820@ e9g2000prf.goog legroups.com...

>>>'YYYYMMDD' is one of the standard, non-regional formats supported by SQL
>>>Server. No special conversion is necessary: <<

>
Picky, picky, but the proper term is "ISO-8601 Standard" and the
Standard SQL format is "yyyy-mm-dd" from that Standard :)
>

[--CELKO--]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

>Conversion necessary because the &%#%$&)# user wants to see mm/dd/yyyy <<

Then do it in the front end like you are supposed to in ANY tiered
architecture; 1NF, basic Software Engineering and all that jazz ...

[David Portas]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

"rdraider" <rdraider@sbcgl obal.netwrote in message
news:Vopzi.5049 6$YL5.29@newssv r29.news.prodig y.net...

Conversion necessary because the &%#%$&)# user wants to see mm/dd/yyyy
>
Thanks
>

Well I assumed that you were storing the date as a DATETIME. What my
suggestion does is to convert a string or integer to a DATETIME. DATETIME
has NO format. So if the user wants to see it formatted some particular way
you must do that in the client application, not in SQL Server. SQL Server
has no control over how the date is displayed.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

rdraider (rdraider@sbcgl obal.net) writes:

Conversion necessary because the &%#%$&)# user wants to see mm/dd/yyyy

Return the date as datetime to the client which then can apply the regional
settings of the client.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tony Rogerson]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

Picky, picky, but the proper term is "ISO-8601 Standard" and the

Standard SQL format is "yyyy-mm-dd" from that Standard :)
>

I doubt I'll get an answer, but yyyy-??-?? don't work in SQL Server so why
do you keep telling people to use it?

select cast( '2007-04-01' as datetime )
select cast( '2007-04-01T00:00:00' as datetime )

Here in the UK in cloudy Harpenden with the default connection settings
gives these results....

-----------------------
2007-01-04 00:00:00.000

(1 row(s) affected)


-----------------------
2007-04-01 00:00:00.000

(1 row(s) affected)

Why do you keep telling people to use yyyy-mm-dd when you have been told
several times of this behaviour?

--
Tony Rogerson, SQL Server MVP

http://sqlblogcasts.com/blogs/tonyrogerson

[Ramblings from the field from a SQL consultant]

http://sqlserverfaq.com

[UK SQL User Community]


"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1187913393 .825664.145820@ e9g2000prf.goog legroups.com...

>>>'YYYYMMDD' is one of the standard, non-regional formats supported by SQL
>>>Server. No special conversion is necessary: <<

>
Picky, picky, but the proper term is "ISO-8601 Standard" and the
Standard SQL format is "yyyy-mm-dd" from that Standard :)
>

[Piero 'Giops' Giorgi]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

On Aug 23, 5:13 pm, "rdraider" <rdrai...@sbcgl obal.netwrote:

Conversion necessary because the &%#%$&)# user wants to see mm/dd/yyyy

What the user wants and what the Db needs are two different things.
You must store the date as a standard and THEN, formatting the output,
you can write whatever you want.

But NOT in the DB

P

[rdraider]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

Unfortunately I have no control over the format of the data in SQL nor the
application. I am however expected to please the users :-/
Time for a new job. I've always wanted to be a pilot...

Thanks for all the help.

RD

"Piero 'Giops' Giorgi" <giorgi.piero@g mail.comwrote in message
news:1187973653 .527587.213280@ m37g2000prh.goo glegroups.com.. .

On Aug 23, 5:13 pm, "rdraider" <rdrai...@sbcgl obal.netwrote:
>

>Conversion necessary because the &%#%$&)# user wants to see mm/dd/yyyy

>
What the user wants and what the Db needs are two different things.
You must store the date as a standard and THEN, formatting the output,
you can write whatever you want.
>
But NOT in the DB
>
P
>

[Erland Sommarskog]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

rdraider (rdraider@sbcgl obal.net) writes:

Unfortunately I have no control over the format of the data in SQL nor the
application. I am however expected to please the users :-/
Time for a new job. I've always wanted to be a pilot...

Yes, that's the point with returning the date as datetime. The client
settings takes full control, so that each user can get the date the way
he prefers.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: Convert CHAR YYYYMMDD to mm/dd/yyyy

>Here in the UK in cloudy Harpenden with the default connection settings gives these results... <<

That's funny -- here in sunny Austin I get the right answer with my
connection settings. Moral to the story "Think globally (i.e. ISO)
and act locally (i.e fix your dialect and proprietary setting)" to
paraphrase the Greens.