SQL Server DataTime Format Question

Question

Post reply

SQL Server DataTime Format Question

Dinesh Ranasinghe

Valued Member

Points: 70
More actions
November 10, 2004 at 11:12 am

#162701

Hi
I am kind of new to sql server stored proc coding. I have a table which has a column with a datatype (DATETIME). and I need to insert a date in MM/YYYY format. Almost all the date format options I have seen so far include the the date. I just need to store the Month and the year, that the user enters. Is there any way to do this? The front end application is aspx with vb.net. Any help or suggestions would be greatly appreciated. Thanks in advance.
Dinesh

Viewing 8 posts - 1 through 7 (of 7 total)

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

Osoba SSCommitted Points: 1964 More actions · Answer 1

If you want to store the date as MM/YYYY, then you will need to store it as a varchar.

If you need to parse out the MM/YYYY in SQL:

cast(datepart(mm,getdate())as varchar)+'/'+cast(datepart(yy,getdate())as varchar)

Adam Machanic SSCoach Points: 15370 More actions · Answer 2

Why not store YYYYMM01 -- First of the month -- and then parse the month and year for display purposes on the client?

--
Adam Machanic
whoisactive

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

Adam is correct... basically, there is no way to store just the Month and Year in a datetime field. In fact, you cannot store a "formatted" date in a datetime field (too long to explain why here). You must also store a day and the first day of the month is as good as any and better than most because every month has a "first day".

You can display the date in the desired format as Osoba suggested or by using the following formula in your SELECT statement:

RIGHT(CONVERT(VARCHAR(10),yourdatecolumn,103),7)

--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)

K Appel SSC Enthusiast Points: 149 More actions · Answer 4

If you have a database with all kinds of dates and want to set it to dd:mm:yyyy:23:59:59.

Thus all the datetimes keep their dates and get the same time: 23:59:29

Is there and easier way than using three functions dateadd,floor and cast?

select pro_created, dateadd(ss,86399,floor( CAST(pro_created AS float)))

from pro

where pro_created is ^not null

Adam Machanic SSCoach Points: 15370 More actions · Answer 5

Why would you use 23:59:59 rather than 00:00:00 ? The latter, IMO, is more intuitive...

--
Adam Machanic
whoisactive

K Appel SSC Enthusiast Points: 149 More actions · Answer 6

The question was to change it to 23:59:59. I did not ask why.

Adam Machanic SSCoach Points: 15370 More actions · Answer 7

Here's another way, slightly more intuitive IMO (as it does not rely on conversion to FLOAT/knowledge of SQL Server's internal date format):

select dateadd(ss, -1, dateadd(dd, 1, datediff(dd, 0, getdate())))

--
Adam Machanic
whoisactive