Date format problem {Resolved}

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date format problem {Resolved}

Author

Topic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 12:25:20

I have a text box that takes the date in the following format: 1/1/1900

In SQL table it shows up as: Jan 1 190

Why is that?

Jack of all trades, Master of none!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-18 : 12:39:36

Try this

Select convert(VArchar(20),'1/1/1900',101)

What are you using for a front end?
What is the field type in SQL?

Jim
Users <> Logic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 12:48:33

VB.NET code from my ASP page:
mycommand.Parameters.Add("@tmpDOB", SqlDbType.DateTime).Value = txtdob.Text

in my Stored procedure its declared @tmpDOB datetime

Jack of all trades, Master of none!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-18 : 12:53:10

What happens if you add this to the front of your SP.
After your @tmpDOB declare

Select @tmpDOB = convert(VArchar(20),@tmpDOB,101)

Jim
Users <> Logic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 13:26:10

Like this?

CREATE Procedure dbo.Insert_Chart_Assignment( @tmpFirstName varchar(50),@tmpLastName varchar(50),@tmpMidInit char(1),@tmpDOB datetime,@tmpDate datetime)AS SET NOCOUNT ON INSERT INTO chartassignment(fname, lname, minitial, dob, [date]) VALUES(@tmpFirstName ,@tmpLastName,@tmpMidInit ,@tmpDOB ,@tmpDate)
Select @tmpDOB = convert(VArchar(20),@tmpDOB,101)
SELECT SCOPE_IDENTITY()
GO

Jack of all trades, Master of none!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-18 : 14:01:19

CREATE Procedure dbo.Insert_Chart_Assignment( @tmpFirstName varchar(50),@tmpLastName varchar(50),@tmpMidInit char(1),@tmpDOB datetime,@tmpDate datetime)AS SET NOCOUNT ON
Select @tmpDOB = convert(VArchar(20),@tmpDOB,101)
INSERT INTO chartassignment(fname, lname, minitial, dob, [date]) VALUES(@tmpFirstName ,@tmpLastName,@tmpMidInit ,@tmpDOB ,@tmpDate)

SELECT SCOPE_IDENTITY()

Does this change the stored date in dob?

Jim
Users <> Logic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 14:18:24

Same results

Jack of all trades, Master of none!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-18 : 15:01:44

What do you mean by "in the sql table it shows up as Jan 1 190" ? Where does it show up like that, in what application? You need to make sure that the date values is being stored properly, not how it looks. And remember that date datatypes do not have any format when stored, the value itself is stored it is up to whatever application is displaying the data to do the formatting.

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-18 : 15:25:16

Sounds like this is happening on your asp page. You said text field, maybe fixed length?

Jim
Users <> Logic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 18:12:04

Fixed!

in my table it was defined as char and I changed it to datetime and it works perfect now.

Thanks for the help JimL!

Jack of all trades, Master of none!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources