Example of storing a DateTime value in a SQL Server database

Anybody have any examples of how to do this? I just want to input the current date in a database.

Thanks in advance
Joe Willy

----------
0
CrunkDaddy
7/19/2002 5:27:18 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
624 Views

Similar Articles

[PageSpeed] 20

two options

1) Set a default value in the table (in design view you set Getdate() in the default field)
2) In an INSERT query

INSERT INTO MyTable (theField) VALUES (Getdate())

0
Dutch
7/20/2002 9:34:36 AM
I know, I wasn't too descriptive. Thank you for your reply. I didn't know about adding

getdate() as a default value.
Actually, what I have is a new user registration form and when they submit their information I want to store it in a SQL Server database. One of the fields in the database is register_date and the format is DateTime.
Using VB.NET in an .aspx page, I don't know how to format the date to store it in the database.
I was extracting the Year, Month, and Day from a DateTime object and then concatenating them into a string to form a new date.
Example:
Public Function formatCurrentDate()
Dim curdate As DateTime
curdate = DateTime.Now
Dim day As Integer = curdate.Day
Dim month As Integer = curdate.Month
Dim dtmformattedDate As DateTime
Dim strDate As String

If day < 10
day = 0 & day
End If

If month < 10
month = 0 & month
End If

strDate = month & day & curdate.Year

dtmformattedDate = Convert.ToDateTime(strDate)

Return dtmformattedDate
End Function

This does not seem to work. I'm really new to all of this so if I'm in the wrong forum please let me know.
Thanks in advance,
CrunkDaddy
Joe Willy

----------
0
CrunkDaddy
7/20/2002 10:08:34 PM
The way a date should be formated is Month/Date/Year. If you do Date/Month/Year, I think you'll need to change a setting in SQL Server (a collation, or something like that).

Do they have to specify Register Date? If its just the date that they registered, when you send the SQL Query over, use Now.Date for the value. VB will send along the current date.

Public Function formatCurrentDate()
Dim curdate As DateTime
curdate = DateTime.Now
Dim day As Integer = curdate.Day
Dim month As Integer = curdate.Month
Dim dtmformattedDate As DateTime
Dim strDate As String
If day < 10
day = 0 & day
End If
If month < 10
month = 0 & month
End If
strDate = month & day & curdate.Year
dtmformattedDate = Convert.ToDateTime(strDate)
Return dtmformattedDate
End Function

--Brian Desmond
Windows Server MVP - Directory Services
http://www.briandesmond.com
0
bdesmond
7/21/2002 2:22:31 PM
Really, to avoid collation problems between the webserver and the database server I tink it's better to just use GetDate()

When you live in the US you don't have those problems, I know, but if you live outside the US working with dates is really a PITA (e.g. English OS on the webserver and Dutch OS on the database server, nice....)
0
Dutch
7/22/2002 5:14:39 AM
CrunkDaddy, it's wasn't my intention to offend you with my 'Huh?' reply, sorry if I did. :-)
0
Dutch
7/22/2002 5:18:31 AM
Not offended at all. Thanks for all your help. I'll let you guys know if I get this to work.


Joe Willy

----------
0
CrunkDaddy
7/24/2002 2:55:19 AM
Hi CrunkDaddy,

The solution is very easy. To avoid any local specific formatting. Just use the ANSI format 'yyyy-mm-dd'. For instance, if you want to store July 24, 2002 in your datetime column:
INSERT INTO myTable (myDateCol) VALUES ('2002-07-24')
The value '2002-07-24' is a string. Now what you have to do in your VB or C# prg is to parse the DateTime value, excerpt the year, month and date to build this string. The ANSI format is insensitive to regional setting or specific collation.
HTH
0
Joco
7/24/2002 3:52:07 PM
When inserting from a web form use this method

If u expect the user will enter the date in the format dmy
set dateformat dmy
insert into table(date_col) values (15/3/2004)
If u expect the user will enter the date in the format mdy
set dateformat mdy
insert into table(date_col) values (3/15/2004)

0
danni
4/5/2004 7:02:45 PM
Reply:

Similar Artilces:

SQL SERVER 2000: In which format the datetime will be stored in sql server 2000?
Hi All, I would like to know, how the datetime will be stored in the sqlserver datetime column. Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy. while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in yyyy/mm/dd format. So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database? Thanks in Advance. Regards, Dhanasekaran. G Storing of datetime depends upon the globalization setting. Normally the date time is stor...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

SQL Server Database File vs SQL Server Database -VWD Express
VWD Express 2008, SQL Server Express 2005. I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE. I am getting started with .NET. Working through a VWD Express book tutori...

How to store a hexadecimal value in sql server database ?
 I need to store a hexadecimal value in a database and use it to assign roles in a library management system.........Give ur suggestions Could you just store it as an integer, then convert it to Hex when you need to process it?~ Timing is Everything! ~   nbsamurai: I need to store a hexadecimal value in a database and use it to assign roles in a library management system.........Give ur suggestionsYou can use varbinary type of column to store the hexa values.  You can cast/convert them to integer as and when required using the cast/convert functions. Thanks,Dhimant Tr...

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'WinFM SQL PFS'. Login fails.
HiASP pages were properly connecting to SQL DB until I restored from a backup DB.  As soon as I finished restoring from the backup DB, I am getting the following error message.   I did not make any changes on ASP side.  IIS Server and SQL 2K Server is running on a WIN2K Server Box.Please help.Thank you.HTTP 500.100 - Internal Server Error - ASP errorInternet Information ServicesTechnical Information (for support personnel)Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login...

saving .net DateTime to sql database using Stored Procedure...
Hello, I've read many posts on this, but not found any real answers. I have a web method that accepts a DateTime parameter, then writes this value back to a SQL database table.  Sql data type is "datetime" and the column accepts nulls. When the web service is called I get an error, "SqlTypeException: SqlDateTime overflow" or another error of similiar results.When I removed the database call and just write to a label or variable on the page, the web method works fine, but when it saves to the database, the error is thrown. Any ideas on how to handle...

How to convert a SQL Server 2005 Database to SQL Server Express
Hi, I have a SQL Server 2005 Database that I would like to export to SQL Server Express. How do I go about this? I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'PRODSOL-LAPTOP1\SQLEXPRESS'.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOp...

How to import excel sheet into SQL Database in SQL Server from Remote SQL Client
 Hi all, I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using  SQL Sever Machine.but it doesn't work using SQL Client  machine.I need to do it using  SQL query.it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.Please tel me how I do this.Following are my code.---------------------------------------------------------------------...

Restoring a SQL Server 2005 database into SQL Server 2000
Hey guys  We currenlty have SQL Server 2000 installed inhouse for testing.  A client has given us a backup of their 2005 database and i'm wondering if the restore will work into 2000, or any issues i should be aware of. Should i just install msde locally on my machine and restore the database?  thanks Hi,Ask your client to backup database as 2000 format. There is an option for this in Microsoft SQL Server Managment Studio. Then download MSSMSE and install for yourself and try to restore database from it.If database doesn't use any 2005 features (rowcount for exampl...

.NET DateTime and SQL Server DateTime problem
.NET DateTime and SQL Server DateTime problem  I have the following if statement in an SQL stored procedure: IF (@Image = 1 AND @DateTimeUploaded = (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))   The value of the @DateTimeUpdated variable was taken from the ImageMainDateTime field when the data was requested by a ASP.NET webpage and is passed back here when that page is submitted so I can compare what the DateTime was when the data was requested with what it is now, in other words I’m doing a concurrency check (I can’t use TimeStamp for many reasons, ...

Copy database from one SQL Server to SQL Server Compact edition
I have a SQL script that creates tables and have insert scripts to put data into it. I want to create the table in Compact SE for my mobile device project. I know I can run a script from VS using SMO, but is there some other way? I have SQL Server Express 2008 installed. From VS itself is there a way to create my tables with data in SQL Server Compact?Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)  In Studio 2008 you can use the Server Explorer to create a connection to an SQL Server 3.5 Compact Edition File, and run queries against it j...

Web resources about - Example of storing a DateTime value in a SQL Server database - asp.net.sql-datasource

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 12/16/2015 6:51:29 PM