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 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
"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
--
"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
--
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
--
>>'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 :)
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 :)
>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 ...
"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
--
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 :)
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
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
>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.
--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
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]
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: MilanB |
last post by:
Hello
How to convert char to int?
Thanks
|
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...
|
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...
|
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
|
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
...
|
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....
|
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...
|
by: Lagon666 |
last post by:
Is any solution to convert "1055901520" to yyyy/mm/dd:
$date = getdate();
print $date; // return 1055901520
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |