Datetime to time conversion with default date

Hi,

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?

Thanks,

Try this in a Derived Column transform (replace DateValue with the name of your column):

Code Snippet

(DT_DBTIMESTAMP)("1900-01-01 " + (DT_WSTR,10)(DT_DBTIME)DateValue)

|||

Thanks, jwelch.

DateTime SQL Server

Hi there!

I terribly need help in understanding what this error means - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Basically, I have the date value from calendar control and inserting it into SQL Server 2000. Any ideas??

Thank you.Read the rror again :-)

Then look at your date. The formatting is wrong and SQL Server tries to mmisinterpret the fields. When it "f%*ks up" with month and date, the date can be 25 - this is out of range for a month.

You need to make sure the datetime you enter is in the correct form.

::I have the date value from calendar control and inserting it into SQL Server 2000. Any
::ideas??

Not matching cultures.

datetime JDBC date conversion

I am writing a datetime field value to MS SQL Server 7 in the following manner
via a stored procedure:

// item to be written is originally a java.util.Date object
java.util.Date fromDate;
// I'm inserting it here into the database
cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));

// the record in the database appears as follows - as I wanted it to..
12/23/2004 4:30:43 AM

The problem is reading the date FROM the database back into ANY type of Java
Date-related object. No matter what I try, the hour/minutes/seconds are not
returned, and I desperately need the hour and minutes. I don't want to store
the hours and minutes in another field - it just causes more complications.
Does anyone out there know a way to get the ENTIRE date value out of the database?Hi

Are you saying that getTime() returns 00:00:00 ?

Posting your the code that tries to retrive this may help!

John

"billb" <kmilburn@.austin.rr.com> wrote in message
news:3561fddb.0410261050.6c8bafba@.posting.google.c om...
> I am writing a datetime field value to MS SQL Server 7 in the following
manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of
Java
> Date-related object. No matter what I try, the hour/minutes/seconds are
not
> returned, and I desperately need the hour and minutes. I don't want to
store
> the hours and minutes in another field - it just causes more
complications.
> Does anyone out there know a way to get the ENTIRE date value out of the
database?|||
billb wrote:

> I am writing a datetime field value to MS SQL Server 7 in the following manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of Java
> Date-related object. No matter what I try, the hour/minutes/seconds are not
> returned, and I desperately need the hour and minutes. I don't want to store
> the hours and minutes in another field - it just causes more complications.
> Does anyone out there know a way to get the ENTIRE date value out of the database?

Are you sure you tried getTimestamp()?
Joe Weinstein at BEA|||billb wrote:
> I am writing a datetime field value to MS SQL Server 7 in the following manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of Java
> Date-related object. No matter what I try, the hour/minutes/seconds are not
> returned, and I desperately need the hour and minutes. I don't want to store
> the hours and minutes in another field - it just causes more complications.
> Does anyone out there know a way to get the ENTIRE date value out of the database?

Convert DATETIME into CHARATER using MS SQL finction is SELECT statement:

CONVERT ( VARCHAR( length ), some_datetime_colimn, <style> )

where <style> is magic number which defines format( 120 ~ yyyy-mm-dd HH:mi:ss);

get value from ResultSet as string and parse it using

java.text.DateFormat.parseDate(...)|||Hi,

Please use the following to construct a full date object from database date
field.

java.util.Date myDate = new
java.util.Date(myResultSet.getTimestamp("myDate").getTime());

I hope this will help ..

Regards,
Yasir

"billb" <kmilburn@.austin.rr.com> wrote in message
news:3561fddb.0410261050.6c8bafba@.posting.google.c om...
> I am writing a datetime field value to MS SQL Server 7 in the following
manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of
Java
> Date-related object. No matter what I try, the hour/minutes/seconds are
not
> returned, and I desperately need the hour and minutes. I don't want to
store
> the hours and minutes in another field - it just causes more
complications.
> Does anyone out there know a way to get the ENTIRE date value out of the
database?

datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

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

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

http://msdn2.microsoft.com/en-us/library/aa176553(SQL.80).aspx

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…

datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

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

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

http://msdn2.microsoft.com/en-us/library/aa176553(SQL.80).aspx

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…

DateTime Format Conversion Issue

Hello everyone,

I came accross this problem where I can't change my stored date to:

m/d/yyyy hh:mi:ss AM

Seems to me like there is no code related to that particular conversion.

I'm just assuming that because I've tried all the codes supplied with the convert, and the closest that I got was

selectCONVERT(nvarchar(max),getdate(),22)

02/27/07 12:24:40 PM

m/d/yy hh:mi:ss AM

So can any one give me a quick solution for this? I'd appreciate it

select

RIGHT(CAST(100+DATEPART(MM,createdDate)ASCHAR(3)),2)+'/'

+RIGHT(CAST(100+DATEPART(DD,createdDate)ASCHAR(3)),2)+'/'

+CAST(DATEPART(YYYY,createdDate)ASCHAR(4))+' '

+CASEWHENDATEPART(HH,createdDate)< 13

THENRIGHT(CAST(100+DATEPART(HH,createdDate)ASCHAR(3)),2)

ELSECAST(DATEPART(HH,createdDate)-12ASCHAR(2))

END+':'

+DATENAME(ss, createdDate)+':'

+RIGHT(CAST(100+DATEPART(MI,createdDate)ASCHAR(3)),2)

+CASEWHENDATEPART(HH,createdDate)< 13

THEN' AM'

ELSE' PM'

END

from files

|||

How about

selectCONVERT(nvarchar(max),getdate(),101)+substring(CONVERT(nvarchar(max),getdate(),22), 9, 50)

?

Dan

datetime error

while executing the following statement:
select convert(datetime,'Nov 31, 2002 10:05pm')
got the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

the datetime set is mdy
is there any way to make this work though this is not according to the iso standarts.

regards,
harsh.Does november contain 31 days ... hmm .. something is very wrong with your code
this works :-
select convert(datetime,'Nov 30, 2002 10:41PM',107)|||Originally posted by Enigma
Does november contain 31 days ... hmm .. something is very wrong with your code
this works :-
select convert(datetime,'Nov 30, 2002 10:41PM',107)

:D :D :D
how foolish of me!!
actually this was the input to a proc and the parameters were provided by the programmers, so i just tried to check with the same.
nways thnxs.
regards,
harsh.

Datetime datatype conversion to int hhmmyy format

Is there a way of converting a datetime data type in the form [DD/MM/YYYY HH:MM:SS] to an integer containing just the time in the form [HHMMSS].SELECT REPLACE((CONVERT(VARCHAR,GETDATE(),24)),':','')

This converts it to a varchar. You actually don't want to convert it to integer as it will drop off the leading zeros.

datetime datatype conversion

Hello

I have 1 column in table with char datatype that stores datetime values (e.g.: 20061207091510 which translates to 2006-12-07 09:15:10).

Is there a way to convert this string into datetime datatype to preserve the time part (hours:minutes:seconds?

Thanks,

Lena

SELECT CONVERT(DATETIME, LEFT('20061207091510', 8), 112)+CONVERT(DATETIME, SUBSTRING('20061207091510', 9, 2) + ':' + SUBSTRING('20061207091510', 11, 2) + ':' + SUBSTRING('20061207091510', 13, 2), 114)

|||thank you!

Datetime conversion under diferent versions of SQL

You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>
... and for some extra reading: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and this is okay on one
> server (which may have British language settings) but not on another (which may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
>

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and this is okay on one
> server (which may have British language settings) but not on another (which may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
>> Hello!
>> I'm using the same script to insert/update records on diferent versions of SQL but i'm getting
>> this error:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de dados char em um tipo de
>> dados datetime resultou em um valor datetime fora do intervalo.
>> (translation: error converting one string into datetime value out of range)
>> The SQL versions that I am probing is 8.00.194 (RTM) that is installed with Microsoft SQL
>> Personal Engine CD and ther other version is 8.00.2039 (SP4) that i've downloaded and installed.
>> Can anywone help me?
>> Regards,
>> kTodos
>

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um
tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de
um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/in...ime.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) a
nd this is okay on one
> server (which may have British language settings) but not on another (whic
h may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates
as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2
MSFTNGP03.phx.gbl...
>

Datetime Conversion Question

If I have a variables that are declared as

@.Start_Year int

@.Start_Month int

How do I convert those variables to datetime format of

MM/1/YYYY

If what you want is datetime:

select convert(datetime, convert(varchar(2), @.start_month)

+ '/1/' + convert(char(4), @.start_year))

If what you want is a formated string in which the month contains a potential leading zero:

select right('0' + convert(varchar(2), @.start_month), 2) +

'/1/' + convert(char(4), @.start_year)

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?
It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>
|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>

datetime conversion question

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ?It appears that the time is not included as part of the date data.
Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.
Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ?
>|||As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.
Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265226240,14397304832
9/19/2006 16:50,EXCEDE,D,147000000000,41808166912
======================================"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O8sLGE3AHHA.3560@.TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@.TK2MSFTNGP03.phx.gbl...
>> Using SQL2005 DTS - I am trying to import data from a CSV file into a
>> table created with the following
>> CREATE TABLE [maint].[dbo].[SpaceAnalysis_v2] (
>> [Date-Time] datetime,
>> [Server] text,
>> [Drive] text,
>> [Drive Size] numeric(29,0),
>> [Space Free] numeric(29,0)
>> )
>> the first field is date and time and looks like this >>
>> 09/20/2006 06:30:03 PM
>> But no matter what I try to use for a final field format the result of
>> that data after it's imported displays the same time for every record >>
>> 12:00:00 AM <<. The date comes through fine, but it just does not seem
>> to recognize the time. What do I need to do to get the time to be
>> imported correctly ?
>

DATETIME conversion problem in stored procedure

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

hi,

dazfl wrote:

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

usually return values other than 0 (zero) indicate a procedure error.. so, 1 is usually read as error and not "success"..

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

try directly consuming a command and relative parameters, like

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandText = "schema.procedureName"

.CommandType = CommandType.StoredProcedure

.CommandTimeout = n

.Connection = connection

Dim p As New SqlClient.SqlParameter

With p

.ParameterName = "@.testvalue"

.SqlDbType = SqlDbType.DateTime

.Value = DateTime.Now

.Direction = ParameterDirection.Input

End With

.Parameters.Add(p)

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

so that you can check (1st important addition of the command and parameters behaviour) and validate parameters initialization... more.. the parameter automatically handles this kind of conversions..

regards

DateTime Conversion Problem

I'm trying to convert a DateTime value from UTC (as it is currently stored
in the database) the the machine's local time.
I tried using:
=Fields!end_dt.Value.ToLocalTime()
And this works so long as there is a DateTime value for the field, but a
warning is thrown if the value is null in the database (this is an optional
field).
I've tried using an iif statment:
=iif(Fields!end_dt.Value is nothing, "", Fields!end_dt.Value.ToLocalTime())
But this still returns the warning as iif evaluates the "false" side whether
or not end_dt contains a value.
Now while the report does render, ideally I don't want any errors or
warnings to be generated, is there any way around this?
Thanks,
JIIF (like all functions) evaluates all of its arguments. So even though
you're testing for Nothing, you're still evaluating
Fields!end_dt.Value.ToLocalTime()
Try this:
=iif(Fields!end_dt.Value is nothing, "", iif( Fields!end_dt.Value is
nothing, DateTime.Parse("12:00"), Fields!end_dt.Value).ToLocalTime())
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"J Smith" <rs-nntp@.jascentral.com> wrote in message
news:%23bx2OmSlEHA.1936@.TK2MSFTNGP12.phx.gbl...
> I'm trying to convert a DateTime value from UTC (as it is currently stored
> in the database) the the machine's local time.
> I tried using:
> =Fields!end_dt.Value.ToLocalTime()
> And this works so long as there is a DateTime value for the field, but a
> warning is thrown if the value is null in the database (this is an
> optional
> field).
> I've tried using an iif statment:
> =iif(Fields!end_dt.Value is nothing, "",
> Fields!end_dt.Value.ToLocalTime())
> But this still returns the warning as iif evaluates the "false" side
> whether
> or not end_dt contains a value.
> Now while the report does render, ideally I don't want any errors or
> warnings to be generated, is there any way around this?
> Thanks,
> J
>

DateTime conversion from GST to EST

I am trying to find a formula to convert the GST to local time EST as well as account for daylight savings time.

We had a formula that was working until this year when the dates on DST changed. (Probably redundant but changed from the 1st Sunday/April thru last Sunday/Oct to 2nd Sunday/March thru 1st Sunday/Nov).

This is what we were doing before; ugly but it worked...

Date
date({PROBSUMMARY1.OPEN_TIME})

DayLite
if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

Time
time({PROBSUMMARY1>OPEN_TIME})-(({@.DayLite})*3600)

DateAdjATL
if Time({PROBSUMMARY1.OPEN_TIME})<=TimeValue(05,00,00)then
DateTimeValue({@.Date}-1,{@.Time}) else DateTimeValue ({@.Date},{@.Time})

We are currently hard coding the dates and we all know how inefficient that is... ANY HELP IS GREATLY APPRECIATED!!!Looks like something is missing...
DayLite
if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

if that is correct, you may need to change the months to

if Month({PROBSUMMARY1.OPEN_TIME}) in 3 to 11 then else 5

Hard to tell....

since you have an if, but no value if the if is true.

if Month({PROBSUMMARY1.OPEN_TIME}) in 4 to 10 then else 5

for example, if Month({prob...}) in 4 to 10 then (What ?) else 5

Datetime conversion from csv file

I have a DTS-package running which imports data from a .csv file to a sql2000 database.
In the file there are some datefields in dd/mm/yyyy format and i want to keep it that way. But after the import the dateformat is yyyy/mm/dd.
Does anybody know how i can prevent this from happening?

Thanks in advanceIn MS-SQL a datetime field is typically displayed as 'yyyy/mm..'. Internally it is stored as an 8-byte value counting from 1973. If you'd like to change the way the datetime is shown, I'd suggest to use 'convert'.