SEO

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

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


"rdraider" <rdraider@sbcglobal.net> wrote in message
news:7Unzi.11028$3x.3766@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" <rdraider@sbcglobal.net> wrote in message
news:7Unzi.11028$3x.3766@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" <rdraider@sbcglobal.net> wrote in message
news:5_nzi.11029$3x.7225@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" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:PZudnSBdYIxykVPbnZ2dnUVZ8qGdnZ2d@giganews.com ...
> "rdraider" <rdraider@sbcglobal.net> wrote in message
> news:5_nzi.11029$3x.7225@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--" <jcelko212@earthlink.net> wrote in message
news:1187913393.825664.145820@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" <rdraider@sbcglobal.net> wrote in message
news:Vopzi.50496$YL5.29@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
--

On Aug 23, 5:13 pm, "rdraider" <rdrai...@sbcglobal.net> wrote:

> 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