Convert CHAR YYYYMMDD to mm/dd/yyyy

#1

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

#2

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

"rdraider" <rdraider (AT) sbcglobal (DOT) net> wrote

Quote:

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

#3

"rdraider" <rdraider (AT) sbcglobal (DOT) net> wrote

Quote:

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/m...S,SQL.90).aspx
--

#4

"rdraider" <rdraider (AT) sbcglobal (DOT) net> wrote

Quote:

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/m...S,SQL.90).aspx
--

#5

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_dportas (AT) acm (DOT) org> wrote

Quote:

"rdraider" <rdraider (AT) sbcglobal (DOT) net> wrote in message
news:5_nzi.11029$3x.7225 (AT) newssvr25 (DOT) news.prodigy.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/m...S,SQL.90).aspx
--

#6

Quote:

'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

#7

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

Thanks

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Quote:

'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

#8

Quote:

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 ...

#9

"rdraider" <rdraider (AT) sbcglobal (DOT) net> wrote

Quote:

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/m...S,SQL.90).aspx
--

#10

rdraider (rdraider (AT) sbcglobal (DOT) net) writes:

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx