How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

How to set the default value for a datetime culomn?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

If you want to insert current datetime by calling getdate() method, then it is possible in sql server everywhere. you can do like this;

insert into employee values (4,'John',25, getdate())

This will insert current datetime in the fourth column (in above example).

Please let me know if it answers your question.

Thanks

Sachin

|||

It is just getdate()

Not (getdate())

|||do u mean in sql werver or in asp.net|||Duh ,

He encapsulated it in an expression which started with a ( and so it ended with a ) It is correct. He did not just use the function getdate() as you indicated here.

how to set default value for datetime column in sql mobile?

I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:

there was a syntax error in the date format. [expression = getdate()]

may be I can only set a exict date ?

I can repro the failure you are seeing. On Searching BOL for sql mobile, It says the following about the default definitions for columns.

DEFAULT Definitions

A column can have only one DEFAULT definition. This can contain constant values or constant functions.

You should post this to the SQL Server Everywhere/Mobile/CE Edition Technical Discussion forum.

How To Set Column Default Value to include Text AND GetDate() ?

GETDATE() works alone as default in a DateTime column, but:

1) How do you include current Date concatenated with text like:

- Column default Value = 'Submitted 2007-07-30 13:15:54.953'

2) is it possible to truncate that DateTime value (deleting the seconds 54.953)

- Final Column default Value = 'Submitted 2007-07-30 13:15'

Thinking Cast or convert but unsuccessful to date:

- 'Submitted ' + Cast(CHAR(16),GETDATE()) ?

I think the short answer here is, 'Yes'; it is just a matter of getting the format that you want. Here is an example:

Code Snippet

declare @.what table (aVc varchar(30) default('Submitted ' + left(convert(varchar, getdate(),120), 16)) )
insert into @.what default values

select * from @.what

/*
aVc
Submitted 2007-07-30 13:15
*/

Giving it another look you might want to try making your default

default('Submitted ' + convert(varchar(16), getdate(),120))

Might simplify the expression a bit.

|||Thanks Kent!

How to separate the DateTime field into Two Fields in View

Hi ,

I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?

Best Regards,

make use of this

DATEPART

Returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Arguments

datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft? SQL Server?.

Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Return Types

int

Remarks

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date' GO

Here is the result set:

Current Date Feb 18 1998 11:46PM SELECT DATEPART(month, GETDATE()) AS 'Month Number' GO

Here is the result set:

Month Number 2

This example assumes the date May 29.

SELECT DATEPART(month, GETDATE()) GO

Here is the result set:

-- 5 (1 row(s) affected)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Here is the result set:

-- 1 1 1900 |||

With datepart you will have to write several statements to getthe correct parts and afterwards join them back concatenating fields.

depending exactly on what you need the correct path is:

a) use datediff as in (if you need dates)

select getdate() /*fulldatetime*/, cast(datediff(day,0,getdate()) as datetime) /*justdate*/, getdate()-cast(datediff(day,0,getdate()) as datetime) /*justtime*/

b) use convert as in (if your ok with strings)

select getdate() /*fulldatetime*/, convert( varchar(20),getdate(),102) /*justdate*/, convert( varchar(20),getdate(),108) /*justtime*/

a) is much faster then (b) and much much faster then datepart solution.

|||

Thanks very much,