Set Property Default in Table. 2

[lars7]

Hi,

New to Sql servers.

Is there any way that I can set the default of a field in my sql table as =format(Date(),"mmm-yy") like I can in an access table. I tried in the default value and binding property of datetime and smalldatetime but I got an "error validating the default for column" message.

 

[rac2]

Are you using Enterprise Manager?

"Binding" doesnt have any meaning in SQL Servr, at least not for setting the default value of a column.

DATETIME is a data type. It has nothing to do with how your application displays data. There are no default settings for displays. This are things that you handle in the application. You can CONVERT data in a query, and you can obtain strings representing date values but that is not part of the definition of the table.

 

[cfStarlight]

lars7,

The getDate() function returns the current date and time. Its like access's now() function. You can use it to set the default value of your column to the current date or the current date and time. But as rac2 mentioned formatting isn't really a part of the table definition the way you're thinking.

Using enterprise manager:

Set the default value to the current date and time
Default Value = getdate()

Set the default value to the current date (no time)
Default Value = (convert(datetime,convert(varchar(10),getdate(),120)))

 

[lars7]

Hi Guys,

I've been working with access for the last couple of year so your datatypes are new to me.

I'm using Sql Server Management Studio Express here at home so that I can set up the database before I take it in to work.

What I am trying to do is set up a monthly data recording system for staffs leave ie, annul leave, sick leave as so on and I'm struggling a bit as to how to record the date.

cfstarlight

I have looked and I cant find the default property in the column only this "default value and binding property" am I looking in the right place?

 

[lars7]

Hi Guys,
I have worked around the problem by making the field nvarchar and creating a date table that will keep the months ordered like dates.

Thanks for your replies.

 

[gmmastros]

lars7,

In my opinion, whenever you want to store a date, you should use the DateTime data type. I have a tingling feeling that you are designing this table wrong. I encourage you to post the table structure and some sample data that you will be storing in this table. Then, a brief description of the reports you expect from this table.

In my (considerable) experience, storing dates in an nvarchar field will cause nothing but trouble in the long run. SQL Server has some really good date functions that you could use so that you can keep that data in DateTime columns but still allow you to group and report the data in the way that you want. Of course, this will take some time to understand, but once you do, the queries are not all that difficult. Furthermore, tek-tips is just a click away, so if you get stuck, you could spend a couple minutes searching for the answer. If you can't find an answer, then you could always post a question. During 'normal' business hours, most questions (that are clearly defined) will get a response within 10 to 15 minutes.

Using dates within SQL Server is no small task. Sure, at first it may seem relatively simple, but there are a couple of complex issues that you should be aware of. Once you understand a few key concepts, eveything will begin to make sense, and you will be happily on your way to using dates.

So, here's what I suggest for you...

Read up on the basics:

faq183-5834
faq183-5842
thread183-1240616

Also, post...

1. The table structure you currently have.
2. Some sample data.
3. Some sample reports (expected outcome).

Dates are an important topic in SQL Server. Spending a couple hours to truely understand them will be time well spent. Trust me.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cfStarlight]

lars7
I have looked and I cant find the default property in the column only this "default value and binding property" am I looking in the right place?

I'm not using that product but "Default Value of Binding property" sounds like the correct property from what I read here

http://msdn2.microsoft.com/en-us/library/ms345150.aspx

I wanted to add that I agree with gmmastros. Storing date values in a varchar column is usually not the right choice. Speaking from personal experince, the time I spent learning about datetimes was definitely worth the effort.

 

[lars7]

Hi gmmastros,

I agree that storing the date as text or (nvarchar) is not ideal but it's the best idea I came up with. The reports I will be doing will be pretty simple (I think) so with the help of my month table I should be Ok, but if you see any major problems I am heading for please let me know.

here is tblLeave script.


CREATE TABLE [dbo].[tblLeave](
[ID] [int] NOT NULL,
[FldMonth] [nvarchar](50) NOT NULL,
[AnnLeave] [float] NULL,
[SickLeave] [float] NULL,
[MatLeave] [float] NULL,
[PatLeave] [float] NULL,
[Studyleave] [float] NULL,
[UnpaidLeave] [float] NULL,
[Other] [float] NULL,
[upsize_ts] [timestamp] NULL,
CONSTRAINT [aaaaatblLeave_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[FldMonth] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and sample data.


ID Fldmonth AnnLeave SickLeave MatLeave
1 Feb-07 4
1 Jan-07 7.5 3.5
2 Feb-07 14
2 Jan-07
and tblmonth script.

CREATE TABLE [dbo].[tblMonth](
[FldDays] [datetime] NULL,
[FldMonth] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_tblMonth] PRIMARY KEY CLUSTERED
(
[FldMonth] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and sample data.

FldDays FldMonth
28/01/2007 Jan-07
28/02/2007 Feb-07
28/03/2007 Mar-07
28/04/2007 Apr-07


I haven't got to the reports yet but I can use FldMonth for display and FldDays to order it as a date.

Thanks for taking the time to reply to me and I will have a look at the faqs and thread you posted.

 

[gmmastros]

I have a couple suggestions for you.

Don't use the float data type. According to Books On Line, float (and real) are Approximate number data types. Instead, I recommend you use Decimal. With Decimal, you specify the precision and scale. The Precision represents the total number of digits that you can store. Scale represents the number of digits to the right of the decimal point. So... Decimal(5,2) has a range of -999.99 to 999.99 For you, I recommend something like Decimal(10,3).

Also, change fldMonth from nvarchar to DateTime. You can still get the format (for display) from a datetime column, but you just need to be a little creative. Let me explain (by example).

[COLOR=green]-- Returns DateTime with Date and Time
[/color][COLOR=blue]Select[/color] [COLOR=#FF00FF]GetDate[/color]()

[COLOR=green]-- Returns
[/color][COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]GetDate[/color](), 100)

[COLOR=green]-- Format the data for report
[/color][COLOR=blue]Select[/color] [COLOR=#FF00FF]Left[/color]([COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]GetDate[/color](), 100), 3) + [COLOR=red]'-'[/color] + [COLOR=#FF00FF]SubString[/color]([COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]GetDate[/color](), 100), 8, 4)

Now, that last bit of craziness works, and works well. So... Whenever you want to show the month value to the user, you could use that crazy formula OR you could wrap this in a function OR you could create a computed column (for display purposes).

The reason I suggest making this a datetime data type is so that you can do date calculations. Suppose you wanted to see the SUM of the leave values for the last 3 months. With DateTime data type, this is easy. With the nvarchar field, not so easy. Suppose you want to see the maternity leave for a particular individual, but sorted by month. To do this with the datetime column, easy. With the nvarchar column, not so easy.

Bottom line... don't store data in the DB to match the output format of a report. This will make the report easy, but will cause problems with everything else.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Here's a method that allows you to have the best of both worlds.

After changing the data type for fldMonth to datetime, you can then add a computed column to the table. This computed column can alter the way the data is displayed. In the script I am about to show, I create a table called TEMP_tblDisplay. I do this so that it won't conflict with your existing table. Notice that the table structure is the same as your original table except for the fldMonth column. Then, I add a couple records. Then, I create a computed column. Then, when selecting the data, you can see that the computed column returns the string you want to display in your reports.

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] [dbo].[TEMP_tblLeave](
    [ID] [[COLOR=blue]int[/color]] NOT NULL,
    [FldMonth] [DateTime] NOT NULL,
    [AnnLeave] [[COLOR=blue]float[/color]] NULL,
    [SickLeave] [[COLOR=blue]float[/color]] NULL,
    [MatLeave] [[COLOR=blue]float[/color]] NULL,
    [PatLeave] [[COLOR=blue]float[/color]] NULL,
    [Studyleave] [[COLOR=blue]float[/color]] NULL,
    [UnpaidLeave] [[COLOR=blue]float[/color]] NULL,
    [Other] [[COLOR=blue]float[/color]] NULL,
    [upsize_ts] [[COLOR=blue]timestamp[/color]] NULL
 [COLOR=blue]CONSTRAINT[/color] [aaaaatblLeave_PK] [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color] [COLOR=blue]NONCLUSTERED[/color] 
(    [ID] [COLOR=#FF00FF]ASC[/color],
    [FldMonth] [COLOR=#FF00FF]DESC[/color]
)
) [COLOR=blue]ON[/color] [[COLOR=blue]PRIMARY[/color]]


[COLOR=blue]insert[/color] [COLOR=blue]into[/color] TEMP_tblLeave(id, fldMonth, AnnLeave, SickLeave) [COLOR=blue]Values[/color](1,[COLOR=red]'20070201'[/color], NULL,4)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] TEMP_tblLeave(id, fldMonth, AnnLeave, SickLeave) [COLOR=blue]Values[/color](1,[COLOR=red]'20070101'[/color], 7.5,3.5)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] TEMP_tblLeave(id, fldMonth, AnnLeave, SickLeave) [COLOR=blue]Values[/color](2,[COLOR=red]'20070201'[/color], 14, NULL)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] TEMP_tblLeave(id, fldMonth, AnnLeave, SickLeave) [COLOR=blue]Values[/color](2,[COLOR=red]'20070101'[/color], NULL, NULL)

Now, create the computed column.

[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] TEMP_tblLeave [COLOR=blue]Add[/color] MonthDisplay [COLOR=blue]As[/color] ([COLOR=#FF00FF]Left[/color]([COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]GetDate[/color](), 100), 3) + [COLOR=red]'-'[/color] + [COLOR=#FF00FF]SubString[/color]([COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]GetDate[/color](), 100), 8, 4))

Now, you'll notice that when you select this new column, it re-formats your date data to that it appears the right way for your reports.

[COLOR=blue]Select[/color] id, 
       fldMonth, 
       MonthDisplay, 
       AnnLeave, 
       SickLeave
[COLOR=blue]From[/color]   TEMP_tblLeave

To remove this table...

Drop Table TEMP_tblLeave

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lars7]

Hi gmmastros,

Thanks I learned some new stuff doing this.

I created the table and added the column but unfortunatly the table went into the master database, not sure how to transfer it to LeaveDatabase. I ran the query you posted too but the DisplayMonth is only putting in this month.

1 2007-02-01 00:00:00.000 Apr-2007 NULL 4
1 2007-01-01 00:00:00.000 Apr-2007 7.5 3.5
2 2007-02-01 00:00:00.000 Apr-2007 14 NULL
2 2007-01-01 00:00:00.000 Apr-2007 NULL NULL

 

[gmmastros]

O.M.G.

Instead of GetDate(), you should use your column name. My apologies for misleading you.

Alter Table TEMP_tblLeave Add MonthDisplay As (Left(Convert(VarChar(20), [!]YourColumnName[/!], 100), 3) + '-' + SubString(Convert(VarChar(20), [!]YourColumnName[/!], 100), 8, 4))

Of course, if you leave it alone and wait another 1/2 month or so, all the values would change to 'May-2007'.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lars7]

Hi Gmmastros,

that worked great:

1 2007-02-01 00:00:00.000 Feb-2007 NULL 4
1 2007-01-01 00:00:00.000 Jan-2007 7.5 3.5
2 2007-02-01 00:00:00.000 Feb-2007 14 NULL
2 2007-01-01 00:00:00.000 Jan-2007 NULL NULL

and of course I didn't need to move the temp table into the LeaveDatabase just create the column in tblLeave :~/

Alter Table dbo.tblLeave Add MonthDisplay As (Left(Convert(VarChar(20), fldmonth, 100), 3) + '-' + SubString(Convert(VarChar(20), fldmonth, 100), 8, 4))

thanks for showing me how to do this and I have learn't that servers are for more than just storing your access tables in? :-D