how to add current date time in bigint field...

Question

I need to add the current date time in a bigint field in a database... and then display from that only the date in format: october 1, 2009.

I am currently thinking of storing the value in string variable and then converting it to int... String s = DateTime.Now.ToString();

i dont know what to do next.. please help

Answer 1

You could just store the number of ticks as your bigint value. Ticks represent the number of elapsed 1/10,000 of milliseconds since January 1, 0001.

DateTime.Now.Ticks;

This can always be converted back to a DateTime by using the constructor that accepts a long:

DateTime storedTime = new DateTime(ticksFromDatabase);

To format your date, just use any of the standard date format strings. A custom format string might work better actually, I just perused them and it doesn't look like there's a built in one for the format you want. This should work:

date1.ToString("MMMM d, yyyy", CultureInfo.CreateSpecificCulture("en-US"))

Answer 2

I'd use a smart date key, since it's easier to find that using SQL:

20090927235000
yyyyMMddhhmmss

This way, if you want to find anything that happened on a given day, you could do:

select * from tbl where datecol between 20090927000000 and 20090927240000

Thereby making data validation a lot easier, even if you are using an ORM.