convert date in GetDate function not working

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)

convert date in GetDate function not working

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-29 : 11:24:58

Snow writes "Hi SQL team,

Here's a weird question for you. I have used convert(varchar, GETDATE(), 103) in my vb application to get the system date in dd/mm/yyyy format. It has worked all along in all modules that require the system date to be saved. However, this statement has failed me in one of my vb module recently. Please see below for the details...

This vb module would save the system date into MS SQL 2000 database after processing. The system date is to be saved into a dd/mm/yyyy format field in the table. I have used the convert function to save the date format in dd/mm/yyyy format in the vb module. But it fails me suddenly! this convert function cannot work suddenly in the module. It prompts the date out of range error. To resolve the problem, I removed the convert function. I.e. only getdate function was used and everything was ok.

This is strange.. as this statement convert(varchar, GETDATE(), 103) is still running fine in the rest of the system. Any explanations for this? Thanks in advance!"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-29 : 12:24:21

Can you post the code you are using around where you're getting the error??

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-29 : 12:36:37

Could be that the field being updated/inserted is a datetime field and the implicit conversion is failing due to it being mm/dd/yyyy and not dd/mm/yyyy

test

create table test1
	(
	TestDate datetime
	)

insert test1
select convert(varchar, GETDATE(), 103)

drop table test1

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-30 : 03:16:10

Are the other functions saving the date into the database as well? I am guessing that while the rest of the database uses a column of datatype varchar, the column in this particular table has a datatype of datetime.

OS

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-30 : 08:31:08

It's not that strange. Are you getting an out-of-range datetime error on the insert? If so, think about why that would happen. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources