MSSQL default field value 'GETDATE() as CCYYMMDD

  • Thread starter anna.predslava@gmail.com
  • Start date
A

anna.predslava@gmail.com

Guest
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

 


> 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@gmail.com> wrote in message
news:1193420956.982387.102830@v23g2000prn.googlegroups.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
>

 
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@gmail.com> wrote in message
news:1193420956.982387.102830@v23g2000prn.googlegroups.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
>


 
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@gmail.com> wrote in message
news:1193420956.982387.102830@v23g2000prn.googlegroups.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
>

 
> 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/SQLServer/content/content.aspx?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@gmail.com> wrote in message
> news:1193420956.982387.102830@v23g2000prn.googlegroups.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
>>
>
>

 

Back
Top