473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Convert CHAR YYYYMMDD to mm/dd/yyyy

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
Aug 23 '07 #1
17 69846
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

Aug 23 '07 #2
"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
--
Aug 23 '07 #3
"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
--
Aug 23 '07 #4
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
--


Aug 23 '07 #5
>>'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 :)

Aug 24 '07 #6
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 :)

Aug 24 '07 #7
>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 ...

Aug 24 '07 #8
"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
--
Aug 24 '07 #9
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
Aug 24 '07 #10
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 :)
Aug 24 '07 #11
On Aug 23, 5:13 pm, "rdraider" <rdrai...@sbcglobal.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

Aug 24 '07 #12
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" <rdrai...@sbcglobal.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

Aug 24 '07 #13
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
Aug 25 '07 #14
>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.

Aug 26 '07 #15
--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
Aug 26 '07 #16
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]

Aug 27 '07 #17
On Aug 24, 3:01 pm, "rdraider" <rdrai...@sbcglobal.netwrote:
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

Aug 29 '07 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Brad Moore | last post by:
Hey all, I'm getting the following compiler error from my code. I was wondering if anyone could help me understand the concept behind it (I actually did try and compile this degenerate...
7
by: MilanB | last post by:
Hello How to convert char to int? Thanks
2
by: Joah Senegal | last post by:
Hello I need to convert a chat to a string... but I don't know how to do this. i;ve searched the internet but I've only find some code to convert char * to string or char to string.... but I...
3
by: simon | last post by:
I am a fresh Visual C++ .NET Developer. Can you kindly guide me for How to Convert char to System::String I am using windows forms and trying to set a text value referencing the method...
1
by: rdraider | last post by:
I can't seem to find a way to convert an INT type in the form of YYYYMMDD to an actual date form of mm/dd/yyyy Can anyone pointt me in the right direction? Thanks
4
by: Ashraf Ansari | last post by:
Hi, How Can I convert MM/dd/yyyy format into dd/MM/yyyy and the date which is converted into dd/MM/yyyy should be in DateTime format. I do not want to store it as a string. Please help ...
2
by: Rasheed | last post by:
Hi, i have a char pointer buffer which will hold the bitmap buffer, So i need to convert char *buffer as a bitmap. becuase when i draw on the Dialog using char *buffer. Nothing will be dispalyed....
3
by: mamul | last post by:
Hi please some one can help me. how to convert char * to string? i have take char *argv from command line and want to pass to a function as string object(string str) i want to first convert argv...
4
by: Lagon666 | last post by:
Is any solution to convert "1055901520" to yyyy/mm/dd: $date = getdate(); print $date; // return 1055901520
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.