TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech Support Archives > Microsoft > Microsoft SQL Server

MSSQL default field value 'GETDATE() as CCYYMMDD

Microsoft SQL Server

 
 
Thread Tools Display Modes
Unread 28-10-2007, 04:02 AM   #1
anna.predslava@gmail.com
Guest
 
Posts: n/a
MSSQL default field value 'GETDATE() as CCYYMMDD

Hello all,
I need to set the current date in CCYYMMDD (ISO) format as the default
value for a field in MS SQL 2000. Basically, when a new record is
inserted into the table the current date needs to be written to a
field in CCYYMMDD (ISO) format.

Please forgive my ingorance if this is a stupid question, I'm from the
UNIX world. MYSQL uses ISO dates by default.

Currently,
- I set the dtate type to (char)
- I set the value to:
GETDATE()
- Then I get this:
'Oct 26 2007 10:27AM'
- I want to get this:
'20071026'

I know MSSQL can read ISO dates,
Does MSSQL have a way to create and store dates in ISO format
(CCYYMMDD)?

Thanks & Warm Regards,
Anna <<<3

 
Unread 28-10-2007, 04:02 AM   #2
Dan Guzman
Guest
 
Posts: n/a
Re: MSSQL default field value 'GETDATE() as CCYYMMDD

> Does MSSQL have a way to create and store dates in ISO format
> (CCYYMMDD)?


CONVERT with a 112 style will convert a datetime value to this ISO string
representation:

SELECT CONVERT(char(8), GETDATE(), 112)

However, you might consider storing dates in native format and performing
string formatting in application code during retrieval.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<anna.predslava***********> wrote in message
news:1193420956.982387.102830@v23g2000prn.googlegr oups.com...
> Hello all,
> I need to set the current date in CCYYMMDD (ISO) format as the default
> value for a field in MS SQL 2000. Basically, when a new record is
> inserted into the table the current date needs to be written to a
> field in CCYYMMDD (ISO) format.
>
> Please forgive my ingorance if this is a stupid question, I'm from the
> UNIX world. MYSQL uses ISO dates by default.
>
> Currently,
> - I set the dtate type to (char)
> - I set the value to:
> GETDATE()
> - Then I get this:
> 'Oct 26 2007 10:27AM'
> - I want to get this:
> '20071026'
>
> I know MSSQL can read ISO dates,
> Does MSSQL have a way to create and store dates in ISO format
> (CCYYMMDD)?
>
> Thanks & Warm Regards,
> Anna <<<3
>


 
Unread 28-10-2007, 06:00 AM   #3
Jay
Guest
 
Posts: n/a
Re: MSSQL default field value 'GETDATE() as CCYYMMDD

Coming from Unix, I understand.

SQL Server only stores dates one way: ccyy-mm-dd hh:mm:ss.mmm - meaning, no
dates, just datetime.

Period.

Now you can use CONVERT() to display it any way you want.

Jay

PS. There are small differences in the exact datetime type and I've been
hearing about an actual date type, but havn't seen/used it yet.

<anna.predslava***********> wrote in message
news:1193420956.982387.102830@v23g2000prn.googlegr oups.com...
> Hello all,
> I need to set the current date in CCYYMMDD (ISO) format as the default
> value for a field in MS SQL 2000. Basically, when a new record is
> inserted into the table the current date needs to be written to a
> field in CCYYMMDD (ISO) format.
>
> Please forgive my ingorance if this is a stupid question, I'm from the
> UNIX world. MYSQL uses ISO dates by default.
>
> Currently,
> - I set the dtate type to (char)
> - I set the value to:
> GETDATE()
> - Then I get this:
> 'Oct 26 2007 10:27AM'
> - I want to get this:
> '20071026'
>
> I know MSSQL can read ISO dates,
> Does MSSQL have a way to create and store dates in ISO format
> (CCYYMMDD)?
>
> Thanks & Warm Regards,
> Anna <<<3
>



 
Unread 28-10-2007, 08:59 AM   #4
Tibor Karaszi
Guest
 
Posts: n/a
Re: MSSQL default field value 'GETDATE() as CCYYMMDD

Below should get you up an running on the datetime datatypes in SQL Server:

http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<anna.predslava***********> wrote in message
news:1193420956.982387.102830@v23g2000prn.googlegr oups.com...
> Hello all,
> I need to set the current date in CCYYMMDD (ISO) format as the default
> value for a field in MS SQL 2000. Basically, when a new record is
> inserted into the table the current date needs to be written to a
> field in CCYYMMDD (ISO) format.
>
> Please forgive my ingorance if this is a stupid question, I'm from the
> UNIX world. MYSQL uses ISO dates by default.
>
> Currently,
> - I set the dtate type to (char)
> - I set the value to:
> GETDATE()
> - Then I get this:
> 'Oct 26 2007 10:27AM'
> - I want to get this:
> '20071026'
>
> I know MSSQL can read ISO dates,
> Does MSSQL have a way to create and store dates in ISO format
> (CCYYMMDD)?
>
> Thanks & Warm Regards,
> Anna <<<3
>


 
Unread 28-10-2007, 02:04 PM   #5
Dan Guzman
Guest
 
Posts: n/a
Re: MSSQL default field value 'GETDATE() as CCYYMMDD

> SQL Server only stores dates one way: ccyy-mm-dd hh:mm:ss.mmm - meaning,
> no dates, just datetime.


Just to be clear, SQL Server datetime and smalldatetime datatypes include
both date and time but this is not how dates are stored. SQL Server stores
datetime values in binary format (integers). The format you see on the
screen is how tools like Query Analyzer and Management Studio choose to
present dates for display purposes.

> PS. There are small differences in the exact datetime type and I've been
> hearing about an actual date type, but havn't seen/used it yet.


New datatypes are planned for SQL 2008 include separate date and time data
types. You can download the latest community technology preview release
from
https://connect.microsoft.com/SQLSer...ContentID=5395

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jay" <spam@nospam.org> wrote in message
news:%23rTYiHSGIHA.6068@TK2MSFTNGP02.phx.gbl...
> Coming from Unix, I understand.
>
> SQL Server only stores dates one way: ccyy-mm-dd hh:mm:ss.mmm - meaning,
> no dates, just datetime.
>
> Period.
>
> Now you can use CONVERT() to display it any way you want.
>
> Jay
>
> PS. There are small differences in the exact datetime type and I've been
> hearing about an actual date type, but havn't seen/used it yet.
>
> <anna.predslava***********> wrote in message
> news:1193420956.982387.102830@v23g2000prn.googlegr oups.com...
>> Hello all,
>> I need to set the current date in CCYYMMDD (ISO) format as the default
>> value for a field in MS SQL 2000. Basically, when a new record is
>> inserted into the table the current date needs to be written to a
>> field in CCYYMMDD (ISO) format.
>>
>> Please forgive my ingorance if this is a stupid question, I'm from the
>> UNIX world. MYSQL uses ISO dates by default.
>>
>> Currently,
>> - I set the dtate type to (char)
>> - I set the value to:
>> GETDATE()
>> - Then I get this:
>> 'Oct 26 2007 10:27AM'
>> - I want to get this:
>> '20071026'
>>
>> I know MSSQL can read ISO dates,
>> Does MSSQL have a way to create and store dates in ISO format
>> (CCYYMMDD)?
>>
>> Thanks & Warm Regards,
>> Anna <<<3
>>

>
>


 
 

Thread Tools
Display Modes



< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 05:28 AM.


vBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO
Copyright © 2005-2013, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional