Can I store only month and year in a smalldatetime column? 2

[cesark]

Hi,

Is it posible to store in a smalldatetime column only month and year? Since I have a field in my web app that the user can specify day, month and year or only month and year.

Thanks

 

[vongrunt]

How about setting day of month to 1?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[jbenson001]

No, the time is always part of any the datetime datatypes. If you insert '02/17/2006' from your app or QA or whatever, without a time, it will add midnight as the time 00:00:00 so you will get '02/17/2006 00:00:00'

Jim

 

[cesark]

Hi vongrunt,

But if I set the default day to '01' how I will able to differentiate between real 01 day and not specified day?

 

[jbenson001]

You won't be able too.. Look in BOL, a smalldatetime will have month day and year and time to the second.

 

[SQLDenis]

add another field (bit field) make it 1 if it's real and 0 if it's not real

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

You could add another field to use to differentiate. Make it a bit datatype and have the value be 1 if it is a complete date and 0 if it is not.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

SQLSister,

plagiarism hahaha
I did to you what George is doing to me all the time....
type faster

;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

She is way too fast!!!

 

[jbenson001]

I should say You are too fast Denis

 

[SQLSister]

Ok you beat me.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cesark]

add another field (bit field) make it 1 if it's real and 0 if it's not real

Ok, done. I suppose it' s a good solution..

Thank you

 

[cesark]

Sorry, and then, to display that field?:

CREATE PROC retrieve_date
@order_id bigint, @date smalldatetime output, @filled_day bit,..
As

Select @date = Case
When filled_day = 1 Then the_date Else [b]??¿¿[/b] End , ...

From orders
Where order_id = @order_id

In the case filled_day = 0 (not filled) I would want to show only month and year, thus: 02/2006 or February/2006. How can I do this? Is there another better solution?

 

[philhege]

Make your output variable a varchar, and CAST the results of the CASE evaluation using DATEPART funcs.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[SQLSister]

Else month(date) + '/' + Year(date)

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cesark]

Thanks, but I tried to format the date once in the web app and works fine. So, I return @filled_day field to app, if it is 0 (day not filled) I format the date to e.g April, 2006, and if it is 1 (day filled) I format the date to e.g. 07/04/2006.