Smalldatetime and GetDate() - SQL Server Forums

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

Transact-SQL (2000)

Smalldatetime and GetDate()

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-07-20 : 07:40:19

I have a column of smalldatetime type.

In a SP, i want to insert date into this column using getdate().

However, If I use getdate() to insert, it would insert date + time into the column

Is there any other function which I can use to insert a date into a smalldatetime column.

regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-20 : 07:50:28

you can use Convert
eg Convert(Varchar,Getdate(),111)

this will not exclude the time just it will insert the default time as 00:00:00 rather then current time.

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 07:51:13

When inserting the date use
dateadd(d,datediff(d,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-07-20 : 08:15:16

thanks
Now I am facing another problem. I am using the following command:

SELECT * FROM MyTable WHERE (MyDate = '31/07/2005')

MyDate is smalldatetime type. However, I get the following error:

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

But if I run:
SELECT * FROM MyTable WHERE (MyDate > '01/01/1900')

It works.

So why the query is not working for '31/07/2005' ???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 08:25:15

It is becuase of local setting
You have to use yyyymmdd format

SELECT * FROM MyTable WHERE MyDate = '20050731'

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-07-20 : 08:39:27

well, every time i run
SELECT * FROM MyTable WHERE MyDate = '20050731'
It hangs-up my system.

I have looked back into my table and found that all of my dates have been chnaged to 'yyyt/mm/dd' format.

madhivanan, What have u made me do???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 09:08:54

It is strange
Check that again
See this on how to search by date
http://vyaskn.tripod.com/searching_date_time_values.htm

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-20 : 09:12:41

Hi
you can try this
SELECT * FROM MyTable WHERE (Convert(Varchar,MyDate,103) = '31/07/2005')

hope this work for you.

Complicated things can be done by simple thinking

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-07-20 : 09:35:30

Thanks chiragkhabaria, that did work.

Final 2 questions.
Why does not (Cast(MyDate as varchar)= '31/07/2005') work???

What does 103 mean in CONVERT(Varchar, MyDate, 103)
regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-20 : 09:37:48

try Convert In BOL you will find the details aboout all the parameters which Convert takes

Complicated things can be done by simple thinking

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-20 : 09:41:37

sorry i didnt read the first question of the last .. since when u cast the sql converts the date in mmm dd yyyy format.. where as the specified format is differnt.. so according to me that didnt worked for you..

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources