Change Date Format of Field value

What is the best way of converting datatime field value 29/03/2005 08:58:27 to 29/03/2005.

I only want to remove Time from date and I am using Sql Server 2000.

Thanks

Arvind

Look at the cast and convert functions

I use cast(convert(varchar(10), getdate(), 101) as datetime)

sql

Change Access Db to server on connection on CR9

I am using VB6 and CR9.

I have some CRs using Access as DB and I am converting them to conect to SQL server DB. What I did now is to change their connection to SQL at design time. But it took long time to reinput the fields on the report since DB has been changed. I wonder if there is better way to do that?

Thanks a lot for any input.try "Database" set datasource location (you will build the new connection) click on table names in both windows, click update. If it finds a match it will map your report to the new location and you won't need to change to many formulas.

Casting or Converting Smallint datatype to Datetime

A SQL Server 2005 db has three date related-columns (MonthGiven,DayGiven, YearGiven) each as smallint datatype. I would like tocreate a DocDate column (datetime datatype) that combines the data fromthe three existing date-related columns. I have tried casting andsimple concatentation without success.

ALTER TABLE Details ADD DocDate DateTime NULL

UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)

I think I need to be doing a Conversion instead of casting buthave been unable to implement info I have found in the SQL ServerDeveloper Center in my situation.

I think this should work, it works for me

UPDATE Details SET DocDate = CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)

|||

You can also use

UPDATE Details SET DocDate = CONVERT(DateTime, CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar), 101)

The last parameter (style) will change based on the input string to convert

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||

Thanks for your prompt suggestions. I tried both and got the same error message:

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

Sinceboth produced the same error, I must assume that either Imisrepresented my data or there is something within the data itselfthat is producing the error. I rechecked the datatypes, etc of myoriginal post and think that is ok.

I queried the data: SELECT MonthGiven, DayGiven, YearGiven FROM Details

When a month, day, or year is unknown the data value = 0

Iam guessing that is causing the error. If so, should i replacethose values with NULL or a string of 0s (Month = 00, Day = 00, Year =0000)?

Thanks again for your input and any further assistance you can provide.

|||

To handle the null values, you can add a where clause to the update statement (add extra conditions in case other illegal values are expected)

Update ...
where MonthGiven is not null and DayGiven is not null and YearGiven is not null

|||

Prashant,

Here is my TSQL:

ALTER TABLE Details ADD DocDate DateTime NULL

UPDATE Details SET DocDate = CONVERT(DateTime, CAST(MonthGiven ASVarchar)+ '/' + CAST(DayGiven AS Varchar) + '/' + Cast(YearGivenAs Varchar), 101) WHERE MonthGiven is not null and DayGiven isnot null and YearGiven is not null

Here is the error:

Conversion failed when converting datetime from character string.

|||

vish4forum:

I think this should work, it works for me

UPDATE Details SET DocDate = CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)

I think you can modify your table this way:

decare @.time varchar(100)

set @.time=CAST(MonthGiven AS Varchar)+ '/' + CAST(DayGiven AS Varchar) +'/' +Cast(YearGiven As Varchar)

update details set docdate=convert(datetime,@.time)

This should work well.

Hope my suggestion helps

|||

Hi B.C.,

I corrected spelling of declare and ran your query with this result:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'MonthGiven'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'DayGiven'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'YearGiven'.

|||

I am trying another approach which may simplify this problem.

ALTER TABLE Details ADD DocDate varchar(20) NULL

UPDATE Details SET DocDate = CAST(MonthGiven AS VARCHAR(5) )+ '/' +CAST(DayGiven AS VARCHAR(5)) + '/' + CAST(YearGiven AS VARCHAR(5))

Sonow I have a DocDate column in the Details table with varchar data(e.g., 9/12/2007)) that simply needs to be cast as datetime. Howcan I do that?

|||

This query executed successfully, but in Object Explorer the data incolumn DocDate still shows as VarChar(20) after refreshing thetable.

UPDATE Details SET DocDate=CONVERT(Datetime,101)

Iwas expecting it to be datetime. Any thoughts on why the queryexecuted successfully but the datatype did not change in ObjectExplorer?

|||

Hi moonshadow,

My fault. The solution i gave above is wrong. You cannot use a intermedia variable in this case.

You can try the suggestionPrashant Kumar provided above. That should work. And as to your question, "UPDATE Details SET DocDate=CONVERT(Datetime,101)

I was expecting it to be datetime " , you cannot do that-- the string which you want to convert to datetime must follow a certain format, for example, 9/12/2007 or 2007-9-12. Based on my understanding, i think convert 101 to datetime value dosn't make sense.The right format is : convert(datetime, '9/12/2007') after which you will get a datetime value.

I would suggest you reading some materials on sql datetime. You will find that's very helpful to solve your problem. thanks

|||

Thanks to all for your patience and very helpful advice.

Casting char to int but not causing error

Hi. In the where clause of my query I will be converting a char column to an
int to match it to another int column. But the char could contain characters
and not convert to an int. In that case I don't want the query to fail but
simply not do the match. Is that possible?
select * from A, B where cast ( A.CharCol as int ) = B.IntCol
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('441')
INSERT INTO #Test VALUES ('55a')
SELECT CAST(col AS INT) FROM #Test
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '55a' to a column of data type
int.
SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
You can visit at Aaron's web site www.aspfaq.com to find a script as
alternative to ISNUMERIC() function
"McGy" <anon@.anon.com> wrote in message
news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
> Hi. In the where clause of my query I will be converting a char column to
> an
> int to match it to another int column. But the char could contain
> characters
> and not convert to an int. In that case I don't want the query to fail but
> simply not do the match. Is that possible?
> select * from A, B where cast ( A.CharCol as int ) = B.IntCol
> Thanks.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Thanks Uri. Unfortunately your example does not work for me. I am not
selecting the char column as an int but rather using it in the where clause
as an int.
Thankfully I have just figured it out myself using the AND clause as
follows - try with A set to 'a' then A set to '1':
declare @.A as char (1)
set @.A = 'a'
select
1
where
( isnumeric ( @.A ) = 1 )
and
( cast ( @.A as int ) = 1 )
McGy
[url]http://mcgy.blogspot.com[/url]
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('441')
> INSERT INTO #Test VALUES ('55a')
> SELECT CAST(col AS INT) FROM #Test
> --Server: Msg 245, Level 16, State 1, Line 1
> --Syntax error converting the varchar value '55a' to a column of data
type
> int.
> SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
>
> You can visit at Aaron's web site www.aspfaq.com to find a script as
> alternative to ISNUMERIC() function
>
> "McGy" <anon@.anon.com> wrote in message
> news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
to
but
>|||Hi
Actually ,I only tried to give you an idea for solving the problem.
"McGy" <anon@.anon.com> wrote in message
news:eDZ8cA2wFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri. Unfortunately your example does not work for me. I am not
> selecting the char column as an int but rather using it in the where
> clause
> as an int.
> Thankfully I have just figured it out myself using the AND clause as
> follows - try with A set to 'a' then A set to '1':
> declare @.A as char (1)
> set @.A = 'a'
> select
> 1
> where
> ( isnumeric ( @.A ) = 1 )
> and
> ( cast ( @.A as int ) = 1 )
>
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> type
> to
> but
>|||Hi Uri,
isNumeric() doesn't work in all cases. For example:
select isnumeric('34e5') -- return 1
select cast('34e5' as int) -- return error
Back to your problem, try this:
select * from A, B
where case when A.CharCol not like '%^[0-9]%' then null else cast (
A.CharCol as int ) end = B.IntCol|||Thanks for that clarification!
McGy
[url]http://mcgy.blogspot.com[/url]
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Tam
I you read my post carefully , you would see what I wrote at the ned of the
post
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Uri,
indeed I didn't read your post carefully. My post was meant to the
original poster ( = McGy), but I thoguth it was you ;)
cheers,

Cast/Convert value to VB variable

Hi all,
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.

Thanks in advance for all your help.

Regards:
Prathmeshe.g.

set rs=cmd.execute("select cast(title as int) as title from xyz")

title=rs.fields("title")|||Thanks for the reply oj. However, my requirement is somewhat different. I'll explain the scenario in short. The user chooses a filename to delete, which is stored in the database as a record. It is stored in 2 places in the database. One as a whole filename and in the second place as a breakdown record. The program should delete the filename from the database and also the file from the disk location. The filename in the database is stored as say "XY006CV003A.xls" or "XY005CJ003B.doc" however on the disk they are stored with the above number and the title for the document, concatenated e.g "XY006CV003A test.xls". So I need to extract the file name only which is "XY006CV003A.xls" to match the database record. The last part "003" is sort of a sequence number and is stored in the database. I need to extract the that sequence to match it and delete it from the second place as I have mentioned. '003' when extracted from the filename will be a string and I need to cast it to Integer type to match the record.

e.g.
fname = Split("XY006CV003A.xls",".") gives "XY006CV003A"
seq = Mid(fname, 8, 3) gives '003' which is string format

I now need to use this seq variable in the query

"Select * from XYZ where fileseq=" & seq

fileseq is of integer datatype so I need to cast/convert seq variable to Integer from String.
Can anybody suggest any ideas?

Regards:
Prathmesh

Cast Question - Converting Datetime into Date

Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.

I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004

Hope you can help me up. Thanks.
-vinceSELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?|||declare @.val datetime
set @.val='12/18/2005 11:00:00'
select @.val, convert(char(10),@.val,101)|||SELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?

I have it here.. Thanks a lot...Sometimes, when you have a lot of things in your mind, you cant see exactly the answer which is sometimes in your face already. ;)

hehehe

CAST error with nvarchar to int (Error converting data type nvarchar to int)

I am receiving an error with a transact query when performing a CAST from an nvarchar to int. For example:

SELECT myField1, CAST(myField2 as int) FROM tbl_myTable

MSDN article describe that this casting error "Error converting data type nvarchar to float" is caused by an invalid non-numeric entry.

Is there a way to perform an in-line test to perform a check to determine a course of action? For example:

SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Are above expression, or something similar to it, even possible in transact-SQL?

Thank you,
Each column in a select stmt can only be one data type. Why do you need to check each row to see if it is an int or not?|||Due to requirements, it is necessary to "transform" data from nvarchar into int (in a massive sweep).

I think I have been able to figure this out using CAST

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASECAST isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||

CAST and CONVERT are the same, CAST is just the standard.

Code Snippet

SELECT field1, CASE WHEN IsNumeric(myField2) = 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM table

anything other than NULL and int wont work though.|||Sorry for a bit of a typo, meant to write the following:

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASE isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||yes, that works fine|||Thank you to everyone who replied!

CASS Certification and Postal Discounts

My organization is moving to a SQL based CRM solution that is customized for
our industry. As a part of this move we are converting all our documents
over to SQL Reports. We have also been contemplating using a CASS certified
product to help us get some substantial postal discounts for our mailings.
Is anyone aware of a product that can take the data being fed into a
reporting server and queue the reports, sort and certify, and then print to
allow postal discounts?
I realize that I am simplifing the process some but I am trying to find out
if there are any vendors servicing this market yet.
--
"Okay, I signed in this box. How do I wipe the ink off my screen now?"I don't know of a product that will do what you want...
However, you might think of this in two parts..
Certifying the addresses in the database,
sorting/printing/printing the CASS Cert documents
The first will be much easier than the second...
In order to get the lowest rate you must print the documents in the same
group together... Docs will print in the order that they complete, so you'd
probably have to buy/write something to group reports into a single printing
batch..
Another thing you might try is to print each group of reports one at a time,
don't start the second group until the first has completed...
In any case - you'll probably have to work very hard if each report is
specific to the address, instead of printing the same report and delivering
it to many people.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Julian I. Spring" wrote:
> My organization is moving to a SQL based CRM solution that is customized for
> our industry. As a part of this move we are converting all our documents
> over to SQL Reports. We have also been contemplating using a CASS certified
> product to help us get some substantial postal discounts for our mailings.
> Is anyone aware of a product that can take the data being fed into a
> reporting server and queue the reports, sort and certify, and then print to
> allow postal discounts?
> I realize that I am simplifing the process some but I am trying to find out
> if there are any vendors servicing this market yet.
> --
> "Okay, I signed in this box. How do I wipe the ink off my screen now?"