connecting to SSAS: datetime

Showing posts with label datetime. Show all posts

Friday, March 30, 2012

Problem in insert a datetime into SqlServer 2005

I user Visual Studio 2005 64 bit ,windowxp 64 bit ,sqlserver 2005

The Sql statement : "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',28/3/2006,4/5/2006,4/5/2006 ) "

and the result in Datebase (All the time become 1/1/1900 0:00:00 )

Although i change the datetype from datetime to smalldatetime the result is same

and i try input the date 28/3/2006 0:00:00 into server but

it show the error:Incorrect syntax near '0'.

What wrong ? help me please,Thank.

They have to be passed as strings:
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test','3/28/2006','5/4/2006','5/4/2006' ) "
or as ISO values which is preferable
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',20060328,20060405,20060405 ) "
HTH, jens Suessmeyer.

|||

Your date is in the future (28th of March this year). That is not allowed:

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 296, Level 16, State 3, Line 5
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

HTH

|||

I prefer to use dates in the following format 'yyyy-mm-dd'.

Have you tried that already (as mentioned above)?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

@.Original Poster: Could you please track the status of the post ? Thanks.

-Jens

sql

Friday, March 23, 2012

Problem in concatinating two paramters to update one column

Using gridview to display the data and sql server 2000

I havea column in the database say departtime of datetime datatype thatcntains the date and time resp(09/19/2007 9:00 PM). I am separating thedate and time parts to display in two different textboxes saytxt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by usingthe convert in sqldatasource. Now i need to update the column in thedatabase and i am using Updatecommand with parameters in aspx lkeupdatecommand = "Update table set departtime = @.departtime" . How cani update my column as datetime by getting the data from 2 texboxes asnow i have 2 textboxes displaying data for single column means if useredit the data in txt1 as(10/19/2007) then on click of update i need topopulate the column daparttime as (10/19/2007 9:00 PM).

Please let me know if you have any questions.

Hello Nick,

What you need is the DateTime.Parse method. See:http://msdn2.microsoft.com/en-us/library/system.datetime.parse(VS.71).aspx

This will create a datetime field of your two text fields.

Jeroen Molenaar.

sql

problem in comparing date

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

Quote:

Originally Posted by cmrhema

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

SQL Server stores dates and time together. Depending on your application and how you are passing your date parameter in as part of your where clause (ie which part of the world you are in) you need to be mindful of your regional settings and look at the CONVERT function in SQL Server

SELECT CONVERT(char(10), GETDATE(), 103)

Will return getdate as character conversion based on the UK regional setting (103)

Regards

Jim :)

Monday, February 20, 2012

problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
markIt definitely seems like you have invalid data in there. How does the strings look like? What format? Also,
you might want to check out: http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column>) = 0
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column>) = 0
i tried that and it returned 0 results
mark|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!|||How many rows do you have?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> > How far in do you get before this error is thrown? Have you check ALL
the
> > rows for the data type. If one row is wrong then the whole process is
> rolled
> > back. What are you using to do the conversion?
> >
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total|||Can you post a zipped CSV of the data in the column?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> > How many rows do you have?
> >
> theres 1144 rows in total
>|||select rowid, convert(datetime, column1) from table1 order by rowid
something similar to that should get you the row or rows that have bad data.
mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine|||mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine
select rowid, convert(datetime, column1) from table1 order by rowid
if you have bad data you'll get an error with that select and then you can
easily track down the row with the bad data. "basically dates" is not good
enough for a data type conversion. sorry if i double posted this.|||This only works if you have a column called "ROWID" I do not believe SQL
server has a rowid column inherent to a table there is a ROWGUIDCOL type but
only if you build your table with it originally.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"ch" <ch@.dontemailme.com> wrote in message
news:40B251FC.A1AC90BF@.dontemailme.com...
> mark wrote:
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > It definitely seems like you have invalid data in there. How does the
> > strings look like? What format? Also,
> > > you might want to check out:
> > http://www.karaszi.com/sqlserver/info_datetime.asp
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> >
> > the strings look like
> > 01/01/1900
> > 01/17/2003
> > etc (ie they are basically dates) - ive checked the data and its fine
> select rowid, convert(datetime, column1) from table1 order by rowid
> if you have bad data you'll get an error with that select and then you can
> easily track down the row with the bad data. "basically dates" is not
good
> enough for a data type conversion. sorry if i double posted this.
>
>|||If Query Analyzer says your data is good, but you're having problems in
Enterprise Manager, try using Query Analyzer to change the datatype using
ALTER TABLE. EM might be doing something else behind the scene.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:BXosc.56$Vp5.20@.newsfe2-win...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> > Hi Mark
> >
> > The data may seem fine when you look at it, but SQL Server might
disagree.
> > You can run have SQL Server inspect the data with the ISDATE( ) function
> to
> > see what values it is not happy with.
> >
> > SELECT <nvarchar column>
> > FROM <mytable>
> > WHERE ISDATE(<nvarchar column>) = 0
> i tried that and it returned 0 results
> mark
>
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:O$5HGtaQEHA.2100@.TK2MSFTNGP11.phx.gbl...
> Can you post a zipped CSV of the data in the column?
>
hi thanks for the help, in the end i exported the data to access (no
problems) converted the field types in access
dropped the data from the sql table - and changed the field types and
re-imported back in - without any hangups
or errors - odd but still its done now!
mark|||select rowid, convert(datetime, column1) from table1 order by rowid
it also only works if you have a table named table1 and that table contains at
least two columns, one column named rowid and another column named column1.
Andrew Madsen wrote:
> This only works if you have a column called "ROWID" I do not believe SQL
> server has a rowid column inherent to a table there is a ROWGUIDCOL type but
> only if you build your table with it originally.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B251FC.A1AC90BF@.dontemailme.com...
> > mark wrote:
> >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > > It definitely seems like you have invalid data in there. How does the
> > > strings look like? What format? Also,
> > > > you might want to check out:
> > > http://www.karaszi.com/sqlserver/info_datetime.asp
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > >
> > > the strings look like
> > > 01/01/1900
> > > 01/17/2003
> > > etc (ie they are basically dates) - ive checked the data and its fine
> >
> > select rowid, convert(datetime, column1) from table1 order by rowid
> > if you have bad data you'll get an error with that select and then you can
> > easily track down the row with the bad data. "basically dates" is not
> good
> > enough for a data type conversion. sorry if i double posted this.
> >
> >
> >|||h
I been in same position once, my date format in string was dd/MM/yyyy
to change the date format you must use convert(datetime,'mm/dd/yyyy') if you change the column format from nvarchar to datetime it will not work. what you should to is create another column with datetime format while your current column is in use. then run a procedure to pick date from the nvarchar date column, use convert and save it into column with datetime format.
Test your data
If everything is fine, backup your table and delete column with nvarchar format
Test your application or modules using that table

Problem converting CHAR to DATETIME

hello all,

I am having a problem calculating the difference, in days, between two dates, STARTDATE and ENDDATE. The data is stored in the database as char(8), formatted YYYYMMDD. "Null" values are stored as '00000000'. When I try to use DATEDIFF an exception is thrown: "the conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"

How can I fix this, and a get a result even if STARTDATE or ENDDATE is '00000000'? Changing the format of the stored data is not an option.

Thanks for any assistance.

Mike

CREATE TABLE dbo.DATETIME1 (
ID1 int,
STARTDATE char(8),
ENDDATE char(8)
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
1, '20070105', '20070108'
)
INSERT into DATETIME1 (ID1, STARTDATE, ENDDATE)
VALUES (
2, '20070105', '00000000'
)
Select * from DATETIME1
Select DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) ) as Difference
from DATETIME1
WHERE ID1 = 1How about a case statement to check for the '00000000'? Or an isdate check?|||Ahhhh, good idea...can you help with the syntax of the CASE statement in SQA? I've done them in VB.NET, but not directly in SQL.

Thanks|||add this to your test code replacing your datediff select.

select STARTDATE, ENDDATE,
CASE
WHEN isdate(ENDDATE) = 0
THEN 0
ELSE
DATEDIFF(d, STARTDATE, Convert(datetime,ENDDATE) )
END
from DATETIME1 as DaysDifference|||THANKS! I was doing something similar, but couldn't quite get it to work! :D|||You could also try the following:
ISNULL(DATEDIFF(d, CONVERT(DATETIME, STARTDATE, 112), CONVERT(DATETIME, NULLIF(ENDDATE, '00000000'), 112), 0)
Btw: explicit conversions are always better than implicit ones:)

Friday, March 30, 2012

Problem in insert a datetime into SqlServer 2005

Friday, March 23, 2012

Problem in concatinating two paramters to update one column

problem in comparing date

Monday, February 20, 2012

problem converting nvarchar column to datetime

problem converting nvarchar column to datetime

problem converting nvarchar column to datetime

Problem converting CHAR to DATETIME

connecting to SSAS

Blog Archive

About Me