Showing posts with label form. Show all posts
Showing posts with label form. Show all posts

Thursday, March 22, 2012

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select '01/'+convert(varchar(2), month(OrderDate))+'/06' from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECT DATEADD(mm, DATEDIFF(mm,0,orderdate), 0) as firstdayofOrdersDates , count(*)as ordercount from orders

group by DATEADD(mm, DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select'01/'+convert(varchar(2),month(OrderDate))+'/06'from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECTDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)as firstdayofOrdersDates ,count(*)as ordercount from orders

groupbyDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

sqlsql

Tuesday, March 20, 2012

Convert word doc to XML and store and retrive form SQL 2000

Hi All,
i want to store the word document files as xml files in the sql server 2000 . then i have to retrive the XML Files in to aspx pages. the document files size are 5 mb , 6 mb like that . plz give the solution that how to do it .
Thanks in Advance .
Regards ,
raja

why do they need to be stored in a database? can't they just be stored as files - maybe index them in the database?

Regards

|||

I agree, why destroy those data caches with big useless blobs?

Monday, March 19, 2012

Convert varchar to date on a column

I have a table with a column defined as a nvarchar. The strings contained in the columns are in the form of YYYYMMDD. I searched the forums here and believe that if I have a date as a string then YYYYMMDD is the correct format for a date stored as string. However, I think I need to store the date as a Date type for selecting, sorting, searching, and indexing. What is the best method of converting the entire column to a Date type from a nvarchar type with the assumption that all the string dates are in the same YYYYMMDD format? When I tried to modify the table using the Managment Studio Express, I get a warning: "Data might be lost converting column 'Date' from 'nvarchar(50)'.

Thanks!

DeBug

Update: I just noticed that all the text fields have double quotes at the start and end of each text string so my so called YYYYMMDD is really "YYYYMMDD". So I guess the answer would need to include how to scrub the " with an UPDATE command. I used the DTS that came with Express to import the data from a flat file.

Moving to TSQL forum. There are many ways i can think of doing this, one of the easiest would be to create a new column, call it DateCol2 with datetime datatype, and then do an update statement on the table to set this column properly. it might involve some parsing, but if you make a mistake, you can always try it again since this is just a new column. Once you've updated it correctly, you can drop the original column and rename the new column appropriately.

Someone here can probably give you the proper update statement.

|||

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

|||

For starters, with datetime datatype you can do arithmatic operations using built-in functions (dateadd, datediff, datefirst, etc.), you can also reference it in multiple ways - "December 27, 2006" or "12/27/2006", or using convert(). You can read more about this datatype in Books Online topic "Using Date and Time Data", http://msdn2.microsoft.com/en-us/library/ms180878.aspx.

|||

Create Table #test(mydate varchar(25))

Alter Table #test

Alter column mydate datetime

drop table #test

Adamus

|||

Doug DeBug wrote:

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

Using the field as a datetime vs. varchar() is the logical thing to do. There's no advantage but only a disadvantage. You can't use operators as comparison and you remove the possibility of using BETWEEN.

Adamus

|||

You can do the convert using UPDATE statement and then do ALTER like:

update tbl

set your_col = replace(your_col, '"', '')

go

alter tbl alter column your_col smalldatetime

go

As for the benefits of using datetime/smalldatetime vs varchar type, the obvious ones are:

1. Appropriate type checking and domain enforcement

2. Better performance

3. Compatibility with other built-in date functions (although they accept strings it depends on the format)

4. Storage depending on how you store the date value in character format

5. Ordering semantics that follow the date or datetime rules

There are cases where you may want to store datetime values in more compact form than using datetime/smalldatetime but those need to be done with care & careful consideration.

|||

Thanks for the example. I will have not used T-SQL as much I should but have relied on the GUIs in the past. I will need a solution to update the customer's database at least once per week. Getting the things like quoted strings out of the way now is a plus.

Regards,

DeBug

Saturday, February 25, 2012

convert question please help

hi experts,
i have coordinates stored in a database in form of 452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 so always take the first number then put the dot then the rest and with the second one take the first 2 numbers put the dot and then the rest, i realized that if i did it with the comma instead of the dot and then convert these 2 in string then it will change the comma to the dot automatically, the result can be also a string.
any help will be very appreciated.

thanksHey mrjoka,

Try this. Hope its what you were lookign for.

Cheers,
Justin

-----------------------
-- Declare variables
declare @.number_1 int
declare @.number_1_len int
declare @.number_2 int
declare @.number_2_len int

-- initialise variables
set @.number_1 = 452031
-- get length of number_1
set @.number_1_len = (select len(@.number_1))
set @.number_2 = 5222500
-- get length of number_2
set @.number_2_len = (select len(@.number_2))

-- Format result
select @.number_1 as old_format, CAST(left(@.number_1, 1) as varchar) + '.' + CAST(right(@.number_1, @.number_1_len-1) as varchar) as new_format
select @.number_2 as old_format, CAST(left(@.number_2, 2) as varchar) + '.' + CAST(right(@.number_2, @.number_1_len-2) as varchar) as new_format|||It's Thanksgiving, so I'm feeling especially NZDF today! Enjoy!-- ptp 20071122 see http://www.dbforums.com/showthread.php?t=1624685

DECLARE @.c VARCHAR(50)

SET @.c = '452031 5222500'

SELECT
Stuff(Left(@.c, CharIndex(' ', @.c)), 2, 0, '.')
, LTrim(Stuff(SubString(@.c, PatIndex('% [0-9]%', @.c), 50), 4, 0, '.'))-PatP|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanks|||hi experts,

Oh, stop it!

Regards,|||you stop it

never mind i found it myself with some string manipulation.

thanks for the help|||Oh geez, I must remember to insert those humour tags next time, that is, if I can remember where I've left them.

Regards,|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanksNo, but if you can post the CREATE TABLE statement, and three or more rows of data, then I could probably "wing it" for you.

The general idea would be to write an UPDATE statement that used your source column name instead of the @.c in my example to set the values for the two new columns using the code that I posted. Once you post the schema and a few rows of data, it should only take a minute to cook up and test.

-PatP|||I'm guessing that he means 452031 5222500 are the two values from the columns X and Y, and he wants a "dot" inserted after the first digit of X and after the second digit of Y. I think.
That Stuff function will work like this:

SELECT STUFF(X, 2, 0, '.') AS X, STUFF(Y, 3, 0, '.') AS Y
FROM location

But if you are going to Update that back into columns of type float, you'll probably need a cast.

Is that what you mean mrjoka?|||452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 update daTable
set x = x / 10000
, y = y / 10000|||set x = x / 10000
, y = y / 10000

That's assuming there are always 6 digits in X and 7 digits in Y.|||mrjoka said:

the result can be also a string
and:
i want just column X and Y to be converted

So its kinda unclear whether he wants to update or not|||That's assuming there are always 6 digits in X and 7 digits in Y.no it isn't :) :)|||no it isn't :) :)Eh?

-PatP|||yes!! another canadian!!

i should have said "no it isn't, eh!!"|||A somewhat belated congrats on 10K, BTW!

I must have missed something though... I think that a-dam has a point, in that Mr. Joka wants to shear two digits off the left side, not N digits from the right side. I think that you also need one more zero on your second divisor.

-PatP|||"shear two digits off the left side" would mean that 452031 and 5222500 would become 45.2031 and 52.22500 and he wanted 4.52031

my interpretation was that the decimal should be moved 5 positions to the left, so from 452031 and 5222500 to 4.52031 and 52.22500

"move n positions to the left" is the same as "divide by nth power of ten"

you are absolutely right, i did not count to 5 correctly

the revised query isupdate daTable
set x = x / 100000
, y = y / 100000

my "no it isn't" comment was in response to "That's assuming there are always [x] digits"

and of course "move n positions to the left" or "divide by nth power of ten" works just as correctly on, say, a three-digit number like 937

:)|||Without the schema, we'll never know just what Mr Joka has now... Without knowing what he's got now, we can choreograph the angels in their dance, but they'll still be confined to the head of the pin.

Mr Joka, set the angels free! In other words, please give us the schema and the sample data I requested many posts ago.

-PatP|||in post #1 he said they are float|||in post #1 he said they are float...and displayed examples of them as one unformatted string!

...and described the output as possibly being a string too!

-PatP|||actually he said "i want to change them" and of course there is no output from an UPDATE

and he said "the result can be also a string" which i took to mean it's optional

:)

Convert question

Hi,
I am importing data form a text file and I have a problem with a column
format
In the file, the numbers are are stored with this format: -54.565,49
Now I have to change the format to -54565.49 to store this value into a
decimal using DTS
Could you help me ?
ThanksDid you transpose the ',' and the '.' ? Is the original format really
-54.565,49 or -54,565.49?
"Javier" wrote:

> Hi,
> I am importing data form a text file and I have a problem with a colum
n
> format
> In the file, the numbers are are stored with this format: -54.565,49
> Now I have to change the format to -54565.49 to store this value into a
> decimal using DTS
> Could you help me ?
> Thanks
>
>|||Yes, the format is -54.565,49
The values are stored in a text file and I can not change it
Thanks
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:7853108E-79F4-4C23-805A-EEE5EDE2781C@.microsoft.com...
> Did you transpose the ',' and the '.' ? Is the original format really
> -54.565,49 or -54,565.49?
>
> "Javier" wrote:
>|||Hi
It may work if you change the regional settings, although you may want to
make the solution non-region specific! One way would to use and activeX
transformation and treat the field as a string. You can then use the replace
function in your script to remove the thousand delimiter and replace the
commas with a decimal point.
John
"Javier" <jvillegas@.elsitio.net> wrote in message
news:erY%23rClCGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am importing data form a text file and I have a problem with a column
> format
> In the file, the numbers are are stored with this format: -54.565,49
> Now I have to change the format to -54565.49 to store this value into a
> decimal using DTS
> Could you help me ?
> Thanks
>|||Using T-SQL, you could use a statement like...
Select select replace(replace(<Column Name>,'.',''),',','.')
The inside Replace function changes the decimal point to nothing at
all. The outside function changes the comma to a decimal point. The
order of operations is very important here.