convert date

Question

Post reply

convert date

CooLDBA

Hall of Fame

Points: 3301
More actions
March 28, 2005 at 12:46 pm

#163967

DECLARE
@t_date varchar(10)
select @t_date=convert(datetime, dob) From dbo.Customers_Log
PRINT(@t_date)
result: Server: Msg 242, Level 16, State 3, Line 16
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
DECLARE
@BirthDate datetime,
@t_date varchar(10)
set @BirthDate = getdate()+5
set @t_date = getdate()+5
Insert into dbo.Customers_Log(dob, dob_old) values
                                (Convert(varchar(10),@BirthDate,101), Convert(datetime, @t_date,101))
result: Server: Msg 242, Level 16, State 3, Line 8
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
DECLARE
@BirthDate datetime,
@t_date varchar(10)
set @BirthDate = getdate()+5
set @t_date = getdate()+5
Insert into dbo.Customers_Log(dob, dob_old) values
                                (Convert(varchar(10),@BirthDate,101), @t_date)

result :
dob               dob_old
04/02/2005       Apr 2 200
but I want my result in dob_old is 04/02/2005
any idea?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Why are you using , 101 when you are doing CONVERT(DATETIME)???? Located in INSERT statement...

If you are converting to VARCHAR(10) change the DATETIME to be VARCHAR(10)...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Bimal Fernando SSCommitted Points: 1945 More actions · Answer 2

This is what is happening

when you have

DECLARE

@BirthDate datetime,

@t_date varchar(10)

set @BirthDate = getdate()+5

set @t_date = getdate()+5

Since you have declared @t_date as varchar(10) sql server automatically converts the Getdate()+5 date to a varchar(10) using the default date style which is "Month Day Year"

Getdate() in varchar Mar 29 2005 invloves 11 characters

But since you have only give 10 characters wide variable it doesn't fit there and truncate the last character.

the correct way to get a 10 character date value is to use

declare @MyDate_Char as varchar(10)

set @MyDate_Char = convert(varchar(10),@MyDate,101)

@MyDate should be a datetime variable

if you want to do it otherway around

declare @MyDate as datetime

set @MyDate = convert(DateTime,@MyDate_Char,101)

@MyDate_Char should be a varchar variable in the following format

MM/DD/YYYY --> 03/29/2005

Hope you now understand the date time conversions

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

See if this helps:

http://www.karaszi.com/SQLServer/info_datetime.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

CooLDBA Hall of Fame Points: 3301 More actions · Answer 4

still gettin the error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The statement has been terminated.

@ExistCount int,

@key int,

@t_date varchar(11)

Select @ExistCount=Count(1), @t_date = dob From dbo.Customers

where custno = @key and

dob <> Convert(varchar(11),@BirthDate,101)

group by dob

Insert into dbo.Customers_Log(custno, dob, dob_old, modified_date) values

(@key, Convert(varchar(11),@BirthDate,101), CONVERT(datetime, @t_date, 101),

Getdate())

table properties:

dob - varchar(11)

dob_old - datetime

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Read the URL I've posted. Style 101 is not considered a save date format. You should rather use 112 or 126.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

CooLDBA Hall of Fame Points: 3301 More actions · Answer 6

CooLDBA

Hall of Fame

Points: 3301

March 30, 2005 at 10:51 am

#549596

But I need in this date format mm/dd/yyyy...

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

It sounds as if you need to display it in that format. Follow what the others have suggested, then at the end, CONVERT( varchar(10), YourDate, 101) for the recordset back to your display...

I wasn't born stupid - I had to study.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

But I need in this date format mm/dd/yyyy...

Then do what Farrell has suggested. But I guess most folks here, including me, consider this a mere presentational issue which you should handle at the client.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

Looks like you're trying to insert the date in the specific format of mm/dd/yyyy ...?

If the column storing the date is a char, then converting dates to a specific style will store the string looking like intended. However, if this is the case - change it! Dates should always be stored in a datetime datatype.

Now, if the column holding the date is a datetime, then you don't need to bother about the formatting, just enter the date in a safe way - SQL Server will then store it correctly for you.

Note that entering a date like '01/02/2005' is not safe! You have not full control over how a string like this will be stored if you leave out the conversion to the default settings. It may be 'mm/dd/yyyy' but it may also very well be 'dd/mm/yyyy' - the point is you don't control it - thus it's the same as introducing a bug in the code.

Preferrably use the ISO style 112 yyyymmdd always when dealing with dates.

..just my .02 of course

/Kenneth