Formatted Date in Default Contstraint

Question

Post reply

Formatted Date in Default Contstraint

mrpolecat

SSCertifiable

Points: 6964
More actions
December 26, 2007 at 10:59 am

#61481

I am trying to set a default contstraint on a varchar field to hold the date in the following format: MMMM dd, yyyy. SO the date appears January 15, 2009. In the past this is done in the DTS pacakge that imports the data because the app requires it in this format but I would like to have the field default to it. Any ideas?
Also it is SQL 7 so a function is not going to work.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

You can use a default value like this:

datename(month,getdate())+' '+

convert(varchar(2),day(getdate()))+', '+

convert(varchar(4),year(getdate()))

mrpolecat SSCertifiable Points: 6964 More actions · Answer 2

Thanks, I found the datename function in one of Phil Factor's articles and came up with this:

DATENAME(month,dateadd(dd,14,getdate())) + ' ' + datename(day,dateadd(dd,14,getdate())) + ', ' + datename(year,dateadd(dd,14,getdate()))

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Heh... to many conversions and concatenation for me... the following does the trick as well...

SELECT STUFF(CONVERT(VARCHAR(30),GETDATE(),107),1,3,DATENAME(mm,GETDATE()))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mrpolecat SSCertifiable Points: 6964 More actions · Answer 4

Thanks Jeff. It cetainly is shorter and looks nicer in my create table sp.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

You bet...

Personally, what I'd rather see is a real DATETIME column and a CALCULATED column to provide the varchar version that the GUI needs. Somewhere down the line, varchar formatted columns with no real DATETIME column to back it up is going to cost you in performance somewhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mrpolecat SSCertifiable Points: 6964 More actions · Answer 6

These are email campaign tables so they only get used once. The app has to have the date formatted for display since it doesn't have tha ability to format data. It suprises me that SQL doesn't have a date format for this since it seems to have every other format.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

I'd still be tempted to make the formatting in a calculated column for the app so I could easily create reports like "How many emails did we send today?". They may not have asked for such reports but wouldn't they be happy if you just happened to offer one up?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mrpolecat SSCertifiable Points: 6964 More actions · Answer 8

Oh , but we have those reports based on actual date fields. The app records the sent date as it sends the emails. This one holds the displayed respond by field and I guess if we wanted to know how many emails needed to be responded to on a certain date then I would want a true date field holding the data. I have the benefit (or detrement?) of being the "They" as well as the "We" so if I have not anticipated my own future desires I can only blame myself.

I do like the concept but I'm not sure the tradeoff of the extra column is worth it in this situation.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Heh... understood... I still kick myself for some of the things I've done to a database in past lives 😀

Wouldn't really be an extra column and no tradeoff that I can see... holds no data... it's just a marker for a calculation... formatted date is "auto-magically" updated when underlying data is changed... for example...

CREATE TABLE #CalcDateExample

(

RowNum INT IDENTITY(1,1),

SomeData VARCHAR(30),

RealDate DATETIME DEFAULT GETDATE(),

CalcDate AS STUFF(CONVERT(VARCHAR(30),RealDate,107),1,3,DATENAME(mm,RealDate))

)

--===== Known dates inserted

INSERT INTO #CalcDateExample

(SomeData, RealDate)

SELECT 'A','01/01/2007' UNION ALL

SELECT 'B','01/02/2007' UNION ALL

SELECT 'C','01/03/2007'

--===== Date but no dates... default date takes over

INSERT INTO #CalcDateExample

(SomeData)

SELECT 'E' UNION ALL

SELECT 'F' UNION ALL

SELECT 'G'

--===== Display the results including the calculated column

SELECT * FROM #CalcDateExample

--===== Do an update to the dates for "B" and "E"

UPDATE #CalcDateExample

SET RealDate = GETDATE()-5

WHERE SomeData IN ('A','E')

--===== Display the results including the calculated column

SELECT * FROM #CalcDateExample

See? No worries about the formatted date.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mrpolecat SSCertifiable Points: 6964 More actions · Answer 10

Banter enough and learn something new. I always thought that the calculated field would still take up space in the database. That said, I guess a datetime will take up less than the varchar(30) I am using now. Thanks for the new knowledge.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Always a pleasure, MrPoleCat... and thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)