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]

Correction the source field in an INT type
So how to convert INT type YYYYMMDD to a date mm/dd/yyyy
"rdraider" <rd******@sbcglobal.netwrote in message
news:7U*****************@newssvr25.news.prodigy.ne t...

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

"rdraider" <rd******@sbcglobal.netwrote in message
news:7U*****************@newssvr25.news.prodigy.ne t...

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

[David Portas]

"rdraider" <rd******@sbcglobal.netwrote in message
news:5_*****************@newssvr25.news.prodigy.ne t...

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

[rdraider]

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" <RE****************************@acm.orgwrote in message
news:PZ******************************@giganews.com ...

"rdraider" <rd******@sbcglobal.netwrote in message
news:5_*****************@newssvr25.news.prodigy.ne t...

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

[--CELKO--]

>>'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]

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

Thanks

"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@e9g2000prf.googlegro ups.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--]

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

"rdraider" <rd******@sbcglobal.netwrote in message
news:Vo****************@newssvr29.news.prodigy.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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

rdraider (rd******@sbcglobal.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, es****@sommarskog.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

[Tony Rogerson]

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--" <jc*******@earthlink.netwrote in message
news:11**********************@e9g2000prf.googlegro ups.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]

On Aug 23, 5:13 pm, "rdraider" <[e-mail]:

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]

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" <gi**********@gmail.comwrote in message
news:11**********************@m37g2000prh.googlegr oups.com...

On Aug 23, 5:13 pm, "rdraider" <[e-mail]:

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

rdraider (rd******@sbcglobal.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, es****@sommarskog.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

[--CELKO--]

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

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

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

And think global means "if it works in where I am, it works"? I know
that it's hard to be humble if you are from Texas, but this is getting
out of hand.

Fact is, in SQL 2005 the format YYYY-MM-DD is subject to local settings
and cannot be trusted, ISO or not. Of course, it was a design made in
California, so I can understand that it's hard for you to swallow.

To soothe you, these two formats are safe:

YYYY-MM-DDThh:mm:ss
YYYY-MM-DDZ

And it seems that when you work with the new date and time data types in
SQL 2000, YYYY-MM-DD is always correctly interpreted.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

[Tony Rogerson]

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.

I see, with YOUR regional settings it works - well that explains it all.

The rest of the planet should adopt Texas Regional settings I guess then.

Perhaps you should alter your statement about regional settings and people
letting the client sort it out then...

This issue is a good marker as to just how professional you really are; now
you know that use the date format YYYY-MM-DD cannot be trusted in SQL
Server, will you keep preaching the use of it?

Or will you use the ISO compliant YYYY-MM-DDT00:00:00 or YYYYMMDD instead?

I look forward to seeing that over the coming months.

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

[Piero 'Giops' Giorgi]

On Aug 24, 3:01 pm, "rdraider" <[e-mail]:

I've always wanted to be a pilot...

I have my PPL (Private Pilot License) since 1982... :-)
You can't imagine how relaxing it is to go fly after a day in front of
the computer screen.

Only downside... damn expensive!!!

Anyway... do not compromise on the data format in the DB.
VERY wrong.

P