Need help converting a CHAR into a Datetime

I have a Database which is having a Counterdate time Column stored in the form of Char(24)

But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error

"Conversion failed when converting datetime from character string."

I am done all permutatiions and combinations for this used

Set @.DE = convert ( datetime, @.ts,121)

Set @.de = cast( @.ts as datetime)

BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime..

I DONT understand why the Server is behaving wiered..

Hoping to get an answer soon.

With regards

Sharad

Database Developer ,

UIC

Hi Sharad,

Can you post a sample of the (character) data that is causing the issue?

Thanks,

Rob

|||

Sir,

I am just using the performance Logs of the a server and in that the database get automatically generated and hence..

I get the data of datetime column in Char(24) form.. and when i use an function to convert that into a datetime form it gives me error..

take anytime .e.g of the form

"2006-11-15 17:33:22.015"

if this is the time stamp and stored in the char(24) format and I want it to be datetime ...

What any sensible person would do

1. cast it into datetime

2. Convert into datetime..

Both not working ..

With regards

Sharad

|||

PLZ reply if u could Help with this simple STUFF

This CODE I RUN

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print @.de

Output is

:::

2006-10-27 20:01:13.297

Oct 27 2006 8:01PM

But i want output to be exactly same

as 2006-10-27 20:01:13.297

Plz help me with this..

|||

change your statement as below,

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print convert(varchar, @.ds, 121)

Need help ASAP with calendar datetime popup

I have a report created in ssrs. In the sql t script I used the between @.startdate and @.enddate statement for a parameter. In my report parameters dropdown I selected the timedate as my datatype. The report works fine if view it from my developement server the calander loads ok. When I view it from any other system the calender Icon is present but with not work. Any suggestions. Is there anything that needs to be put into the webconfig file to make this work. Javascript??

Hi,

Post your code. Someone will have a look at it.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

need help

CREATE TABLE [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] ([Rec_Date_Tm] [datetime] NULL, [QCluster_Count_1_SA2] [int] NULL , [QCluster_Count_2_SA2] [int] NULL , [QCluster_Count_3_SA2] [int] NULL , [QCluster_Count_4_SA2] [int] NULL , [QCluster_Count_5_SA2] [int] NULL , [QCluster_Count_1_SB2] [int] NULL , [QCluster_Count_2_SB2] [int] NULL , [QCluster_Count_3_SB2] [int] NULL , [QCluster_Count_4_SB2] [int] NULL , [QCluster_Count_5_SB2] [int] NULL) ON [PRIMARY]

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] TO [irisaoidb]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] ([lm0805] [varchar] (50) NULL, [rsc725] [varchar] (50) NULL) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] TO [irisaoidb]

INSERT INTO TP_Large_Void_Rawdata_794981854_2007319_10613_7944 SELECT Rec_Date_Tm, QCluster_Count_1_SA2, QCluster_Count_2_SA2, QCluster_Count_3_SA2, QCluster_Count_4_SA2, QCluster_Count_5_SA2, QCluster_Count_1_SB2, QCluster_Count_2_SB2, QCluster_Count_3_SB2, QCluster_Count_4_SB2, QCluster_Count_5_SB2 FROM tbl_IRISAOI_Rawdata_KM1 (nolock),TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944 (nolock) WHERE Rec_Date_Tm >= '2007-03-19 00:00:00' AND Rec_Date_Tm <= '2007-03-19 20:00:00' AND Testcell = TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944.Selected_Value AND Product = TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944.Selected_Value AND Lot_No = TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944.Selected_Value and tester in (ALL) and upper(Lot_Id_Code) in (ALL) and upper(Bin_No) in (ALL) and upper(Grade_No) in (ALL)

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'ALL'.

can anyone point out what mistake that i've made here?

The ALL is a parameter that passed to the stored procedure when invoked.

|||

oops, in fact i've left out the CHAR(39) on both side of the parameter...

now the problem is solved.

sql

Need Expression Help with DateTime Variable

Hello,

I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.

Any ideas?

Thank you for your help!

cdun2

Why do you care if it has a time portion on it?

How are you populating the variable? It'll have to be a string type in order to eliminate the time component.|||

Sorry for the confusion. Actually, the date does need to be cast to a string. I need to append the date to another package variable to complete the 'name' of a flat file. The idea is that when the package runs, the flat file destination file name will contain the current date.

I know how to set up an exression for the 'Name' of the flat file, but what would be the best way to take the result of GETDATE(), cast it to a string, and parse out just the date portion? Since I don't want the non alpha numeric characters in the string, maybe I should just put a date string together using YEAR(GETDATE()) + MONTH(GETDATE()) + DAY(GETDATE()).

If I go this route, how do I used type casting with these date functions?

Thanks again.

|||

Here is my first idea. This would be the expression for the Name property of the flat file Connection Manager;

@.[User::FilePath] + @.[User::FilePrefix] + (DT_STR, 4, 1252) YEAR(GETDATE()) + (DT_STR,2, 1252)MONTH(GETDATE()) + (DT_STR, 2,1252)DAY(GETDATE())

|||

cdun2 wrote:

Here is my first idea. This would be the expression for the Name property of the flat file Connection Manager;

@.[User::FilePath] + @.[User::FilePrefix] + (DT_STR, 4, 1252) YEAR(GETDATE()) + (DT_STR,2, 1252)MONTH(GETDATE()) + (DT_STR, 2,1252)DAY(GETDATE())

I like this idea.

nedd some help with datetime

Hi,
I am passing datetime from my vb.net app for storage in sql server like this
Dim DT As DateTime = DateTime.Now
Dim str As String
str = DT.ToString("MMddyyyyHHmmss")
...insert to database str (example datetime 10312005135802)
The table column datatype is char(15). How can I convert this to datetime in
sql server? I have an sp that need to look at both the date and time. I was
trying this
declare @.dateandtime datetime
select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea where
id = 1234
select @.dateandtime
I am getting the error "syntax error converting datetime from character
string".
Or should I change the format I am sending the datetime from my vb.net app?
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01A96FD0-238C-42A8-AEF5-3BE330EC9EDC@.microsoft.com...
> Hi,
> I am passing datetime from my vb.net app for storage in sql server like
> this
> Dim DT As DateTime = DateTime.Now
> Dim str As String
> str = DT.ToString("MMddyyyyHHmmss")
> ...insert to database str (example datetime 10312005135802)
> The table column datatype is char(15). How can I convert this to datetime
> in
> sql server? I have an sp that need to look at both the date and time. I
> was
> trying this
> declare @.dateandtime datetime
> select @.dateandtime = convert(datetime,datetimecolumn,120) from tablea
> where
> id = 1234
> select @.dateandtime
> I am getting the error "syntax error converting datetime from character
> string".
> Or should I change the format I am sending the datetime from my vb.net
> app?
> Thanks
Any reason why you appear to be using dynamic SQL to do this? Pass the date
as an adDBTimeStamp type using the ADO parameters collection. That way the
conversion to SQL DATETIME is implicit. Also, you should typically call a
proc for a simple INSERT rather than constuct a dynamic SQL string in code.
See the Using Parameters topic in the ADO section of Books Online.
David Portas
SQL Server MVP
--|||> The table column datatype is char(15).
WHY? Do you put ice cream in your medicine cabinet too?
Pass it as a proper date time value, store it as a proper date time value,
and you will be amazed how many of these silly string conversion problems
magically disappear.|||Honestly, I was thinking the same thing, however, I have noticed in a few
articles a while ago where data is stored as strings and taught I was wrong
all along to use datetime type in my database when I need to pass and store
date and time form my front end app.
Thanks for rebooting that section.
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHY? Do you put ice cream in your medicine cabinet too?
> Pass it as a proper date time value, store it as a proper date time value,
> and you will be amazed how many of these silly string conversion problems
> magically disappear.
>
>