conversion from varchar to datetime
-
I have a column that is a datetime. I need to build a date from the start date and start time and insert into this datetime field. I get an error saying:
Server: Msg 241, Level 16, State 1, Line 259
Syntax error converting datetime from character string.
this is the code that I am using in the insert statement for the field:
CONVERT(VARCHAR(10),@dSTART_DATE,101) + '' + convert(varchar(10),@Time,108),
I have several sp's that I am doing that will come across this same issue, so any help is greatly appreciated!! Thanks!! and happy Friday!!
-
You don't give us any sample values for @dSTART_DATE or @Time. So run this, using your values, and show us what the result is:
SELECT CONVERT(VARCHAR(10),@dSTART_DATE,101) + '' + convert(varchar(10),@Time,108)
-SQLBill
-
BTW-I would suggest putting your date in the yyyymmdd format (use 112 vice 101).
-SQLBill
-
No problems with that, except for the fact that from there you want to convert it back to datetime. SQL Server doesn't know what 01/27/2006 equates to. Is it January 27, 2006 or is it the first of the 27th month - which is an invalid date and will return:
Server: Msg 241, Level 16, State 1, Line 259
Syntax error converting datetime from character string.
Two solutions. Use either my recommedation of using style 112 instead of 101.
SELECT CONVERT(VARCHAR(10),@dSTART_DATE,112) + ' ' + convert(varchar(10),@Time,108)
Or begin your script with SET DATEFORMAT command. That tells SQL Server how to interpret dates.
SET DATEFORMAT MDY
SELECT CONVERT(VARCHAR(10),@dSTART_DATE,101) + ' ' + convert(varchar(10),@Time,108)
-SQLBill
-
Maybe I'm reading this wrong, but you want build a date from a date and a time and insert into a datetime field. So just do one more conversion to datetime...
declare @dSTART_DATE datetime
declare @Time datetime
set @dStart_Date= getdate()
Set @Time = '08:26:00'
SELECT CONVERT(datetime, CONVERT(VARCHAR(10),@dSTART_DATE,101) + ' ' + convert(varchar(10),@Time,108))
-
Are you sure you've got the right section of code?
-
Are you sure all your values ARE dates? Run this:
SELECT ISDATE(d_StartDate)
FROM tablename
-SQLBill
-
BTW-If you get a 0 (zero) returned, it means there are values that are not dates. If you get a 1 (one) then all values are dates.
If the 0 is returned, you will have to try and figure out which values are not dates.
-SQLBill
-
You run this:
declare @dSTART_DATE datetime
declare @Time datetime
set @dStart_Date= getdate()
Set @Time = '08:26:00'
SELECT CONVERT(VARCHAR(10),@dSTART_DATE,101) + ' ' + convert(varchar(10),@Time,108)
and get the error. Of course you do...you are trying to convert 08:26:00 to datetime. SQL Server thinks that is a DATE not a time. Try this:
declare @dSTART_DATE datetime
declare @Time VARCHAR(8)
set @dStart_Date= getdate()
Set @Time = '08:26:00'
SELECT CONVERT(VARCHAR(10),@dSTART_DATE,101) + ' ' + @Time
-SQLBill
-
I also had the same error message when i tried to concatanate a string with DATETIME converted to VARCHAR and it worked when i used a REPLACE function to replace the spaces in the date with under scores. I am posting this hoping to help ppl with similar problems. My code is below
DECLARE @date DATETIME
DECLARE @location VARCHAR (100)
DECLARE @dateconverted VARCHAR (32)
SET @date = SUBSTRING ((CAST (getdate() AS VARCHAR (20))), 1, 12)
SET @dateconverted = REPLACE (@date, ' ', '-')
SET @location = 'c:\DataBaseBackups\DBBackUps\LMUserFB'+ '_'+ @dateconverted + '.BAK'
-
Since he's converting to DateTime, I'd suggest doing neither.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply