DateTime unable to save in datetime field of SQL database

Hi all, having a little problem with saving dates to sql database

I've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up

Error "

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

I've tried researching it but not been able to find something similar.

Heres the code:

DateTime createOn = DateTime.Now;

string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')";

Any help would be much appreciated

If you are using SQL Server, change the statement to

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',GetDate())

If you are using Access then use this:

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',Date())

|||

Sorry, my fault i should have said, i'm coding in c sharp, heres the expanded function

void AddToQuizIndex(String userName,String quizName,String description,String question_xml)

{

DateTime createOn = DateTime.Now;

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"','" +createOn+"','" + question_xml +"')";this.ActionSQLStatement(sSQLStatement);

}

|||

C# makes no difference. GetDate() in SQL Server will automatically apply the equivalent of C# datetime.now. But your database won't complain. Try it.

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"',GetDate(),'" + question_xml +"')";

Really, you should be using parameters rather than compiling dynamic SQL statements, but that's another topic.

|||

nice one, first time i tried it i didn't put ' ' round the GetDate()

Thanks very much for the replyMikesdotnetting, you really helped me out.

DateTime types and getdate() comparison

SQL 2000. Let's say column MyDate is a datetime type. Is this
comparison syntax OK as is?
... where MyDate <= getdate()
Or is some formatting of the column value and/or of the function's
return value required for the comparison to work?
Thanks
LiamComparison operators (<,>,=, <>, >=, <= ) are allowed between two values wit
h
a datatype of datetime. Your expression is fine.
However, if you want to do things like add or subtract datetime values, you
will need to use the date and time functions in SQL Server.
"Liam" wrote:

> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> .... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam
>|||depends on what you need
but don't convert the column - you'll lose any sargability if it's indexed.
i tend not to try to rely on date data having being inserted with a time
of midnight, so i convert the variable and perform range queries
if you need mydate <= just the date: then do
MyDate < tomorrow at midnight
e.g.
where MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need MyDate for just today
where MyDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need mydate <= current date and time, then simply using
getdate() is appropriate.
Liam wrote:
> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> ... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam

datetime type for debugging store procedure

Some of the pass-in parameters in my store procedure are datetime
type. I have tried to enter parameters in the debug window in the
QueryAnalyzer, but everytime it came back with error
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
The parameters I have tried are:
1/1/1998
'1/1/1998'
#1/1/1998#
What should I enter in the debug window for the datetime?
Thanks,
AlanTry '19980101'
Andrew J. Kelly
SQL Server MVP
"Alan" <alan_test@.yahoo.com> wrote in message
news:rdhjgvs83vclf99359l9c1sjm6d3q76c2k@.4ax.com...
> Some of the pass-in parameters in my store procedure are datetime
> type. I have tried to enter parameters in the debug window in the
> QueryAnalyzer, but everytime it came back with error
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
> The parameters I have tried are:
> 1/1/1998
> '1/1/1998'
> #1/1/1998#
> What should I enter in the debug window for the datetime?
> Thanks,
> Alan|||Thanks for reply, but I just tried both
'19980101'
19980101
It came back with same error message.
Alan
On Mon, 7 Jul 2003 15:24:41 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Try '19980101'|||Sorry. I remember now that you must use the format as follows (Taken from
BOL):
{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
{ t 'hh:mm:ss'} such as: { t '10:02:20'}
This is the international way to format dates and times in ODBC and OLEDB.
--
Andrew J. Kelly
SQL Server MVP
"Alan" <alan_test@.yahoo.com> wrote in message
news:qrjjgvoiu0oj3bjon5fk7mt8l565ujevpj@.4ax.com...
> Thanks for reply, but I just tried both
> '19980101'
> 19980101
> It came back with same error message.
>
> Alan
> On Mon, 7 Jul 2003 15:24:41 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
> >Try '19980101'
>|||1998-09-24 10:02:20 works.
Thanks a lot.
Alan
On Mon, 7 Jul 2003 17:18:38 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Sorry. I remember now that you must use the format as follows (Taken from
>BOL):
>{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
>{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
>{ t 'hh:mm:ss'} such as: { t '10:02:20'}
>
>This is the international way to format dates and times in ODBC and OLEDB.sql

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 to date

Hi, everybody.
I have a parameter from datetime type.
Its' values are stored with date and time values together in the database.
But I only want to show its' date value to the user.
How can I do this?

You can modify the way a Datetime value is shown by adding the ToString() expression.

Take a look at the DateTime.ToString() reference: http://msdn2.microsoft.com/en-us/library/system.datetime.tostring.aspx

|||Thanks Jan.
But my parameter's value is not shown in the report.
So I can't write an expression or a method to this.( as Datetime.ToString() )
I think I should do this by SQL side.
But when I use a convert or cast function on sql side , there exists a type conflict.
Anyway, if you find a solution, share it with me please.|||

If you are using the datetime values as a parameter, the easiest way to do this is to create or modify your parameter query: add a column with only the date value of the other datetime values.

SELECT Date AS DBDate, CONVERT(VARCHAR(10), Date, 101) AS ViewDate
FROM <<Table>>

This query shows also the date in the format mm/dd/yyyy. Then use this query for the parameter: use the DBDate as Value and the ViewDate as label.

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 Parameters reset to String

When I go to Report > Report Parameters... in VS, I create date parameters and specify the type as datetime. I then add the parameters to my SQL statement. I then go back to look at the parameters and they have changed to string. This happens every time I make a modification to the SQL statement. Is this a bug, or am I missing a step? I thought someone would have asked this before, but I can't find a post for it in this forum.

Thanks in advance,

Scott

Parameter's type can change to string, but it should only happen once. What version are you using?|||I'm using 2005. I'm not 100% sure but I'm fairly certain that it happens every time I change the SQL statement.|||Its Not a bug, Its a Feature :)))

But yes - I don belive its a bug.
It happened to me a few time that when I played with the dataset its changed my parameter type from dateTime to string.

If this will be your only problem with the dateTime parameter you should be happy.
Its also have some problems with some localization sometimes....

DateTime Parameter Validation

Hello Group,
I am having a report parameter which is of the type DateTime. I get a simple
textbox for that and if the user types in some invalide date an exception is
thrown on the UI. I want to handle this parameter as soon as user types it
in and clicks on the 'View Report' button. Can i some how pass this typed-in
value to the VB.Net code that i will write in the rdl and validate it there
itself? I can pass it to my stored proc by making the type of the parameter
as 'String' and do the validation there. But don't want to pass this value
to the stored proc.
Thanks in advance
AtulI have an example on www.msbicentral of a method to use a string as a data
parameter, but format it using functions... ( the name is something like
formatted date...)
I wonder if you couldn't do the same as in the example, except instead of
calling the format method in VB.net, call Code.whatever, and use a code
function which you have written...I've never done exactly that but it might
be worth a few minutes to try...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Atul" <atul.anjankar@.approva.net> wrote in message
news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hello Group,
> I am having a report parameter which is of the type DateTime. I get a
> simple textbox for that and if the user types in some invalide date an
> exception is thrown on the UI. I want to handle this parameter as soon as
> user types it in and clicks on the 'View Report' button. Can i some how
> pass this typed-in value to the VB.Net code that i will write in the rdl
> and validate it there itself? I can pass it to my stored proc by making
> the type of the parameter as 'String' and do the validation there. But
> don't want to pass this value to the stored proc.
> Thanks in advance
> Atul
>|||Is there a reason you are using text for the datetime parameter? You can
have it be datetime. Menu Report, Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Atul" <atul.anjankar@.approva.net> wrote in message
news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hello Group,
> I am having a report parameter which is of the type DateTime. I get a
simple
> textbox for that and if the user types in some invalide date an exception
is
> thrown on the UI. I want to handle this parameter as soon as user types it
> in and clicks on the 'View Report' button. Can i some how pass this
typed-in
> value to the VB.Net code that i will write in the rdl and validate it
there
> itself? I can pass it to my stored proc by making the type of the
parameter
> as 'String' and do the validation there. But don't want to pass this value
> to the stored proc.
> Thanks in advance
> Atul
>|||Hello Bruce,
If i keep the parameter as datetime, and if the user types in some invalid
date, MSSSRS throws and exception on the UI. I using MSSSRS's UI for the
parameters' input and not any aspx page. If i want to avoid the exception i
will have to accept it as a string and pass it to the stored proc and chekc
the validity of the user input there.
Atul
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e0$ga%23nGFHA.3076@.tk2msftngp13.phx.gbl...
> Is there a reason you are using text for the datetime parameter? You can
> have it be datetime. Menu Report, Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Atul" <atul.anjankar@.approva.net> wrote in message
> news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Hello Group,
>> I am having a report parameter which is of the type DateTime. I get a
> simple
>> textbox for that and if the user types in some invalide date an exception
> is
>> thrown on the UI. I want to handle this parameter as soon as user types
>> it
>> in and clicks on the 'View Report' button. Can i some how pass this
> typed-in
>> value to the VB.Net code that i will write in the rdl and validate it
> there
>> itself? I can pass it to my stored proc by making the type of the
> parameter
>> as 'String' and do the validation there. But don't want to pass this
>> value
>> to the stored proc.
>> Thanks in advance
>> Atul
>>
>|||Your choice is to accept the UI from the Report Manager (which in most cases
what I do) or pass it to the SP for validation.
True, this "The value provided for the report parameter 'StartDate' is not
valid for its type. " is not the most user friendly message.
Still, I haven't really had a problem with the users. I'm afraid that we
don't have a whole lot of options with this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Atul" <atul.anjankar@.approva.net> wrote in message
news:uugSVIzHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> Hello Bruce,
> If i keep the parameter as datetime, and if the user types in some invalid
> date, MSSSRS throws and exception on the UI. I using MSSSRS's UI for the
> parameters' input and not any aspx page. If i want to avoid the exception
i
> will have to accept it as a string and pass it to the stored proc and
chekc
> the validity of the user input there.
>
> Atul
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e0$ga%23nGFHA.3076@.tk2msftngp13.phx.gbl...
> > Is there a reason you are using text for the datetime parameter? You can
> > have it be datetime. Menu Report, Parameters
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Atul" <atul.anjankar@.approva.net> wrote in message
> > news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> Hello Group,
> >> I am having a report parameter which is of the type DateTime. I get a
> > simple
> >> textbox for that and if the user types in some invalide date an
exception
> > is
> >> thrown on the UI. I want to handle this parameter as soon as user types
> >> it
> >> in and clicks on the 'View Report' button. Can i some how pass this
> > typed-in
> >> value to the VB.Net code that i will write in the rdl and validate it
> > there
> >> itself? I can pass it to my stored proc by making the type of the
> > parameter
> >> as 'String' and do the validation there. But don't want to pass this
> >> value
> >> to the stored proc.
> >>
> >> Thanks in advance
> >> Atul
> >>
> >>
> >
> >
>sql

Datetime Parameter

Hi All,
I have a report with two parameters. Date1 and date2 and both are of type
datetime. when I select a date greater than 12/05/2006 the report fails
stating "the value provided for the report parameter Date2 is not valid for
its type". Now date1 has 01/05/2006 and date2 has 31/05/2006. For the
format is Australian date format and I have checked my regional settings and
they are set correctly to Australian and I have done the same on the report
server.
What is going on and how do I fix it....
Thanks
MichaelHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From you description, my understanding of this issue is: You want to
transfer the date-time parameter in (dd/mm/yyyy) format in to the report.
If I misunderstood your concern, please feel free to point it out.
By default, it's not possible to set the format of the date. However I did
a workaround for you, you can set the parameter data type to string then
use the SQL Convert() function to convert the parameter to DateTime.
The simple example of this will be like this.
Select * from orders where orderDate= CONVERT(DateTime, @.mydate, 103)
Here is the article about the CONVERT function.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
ca-co_2f3o.asp
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael/Wei
I have found the exact same problem, here's what we have discovered.
The problem is that RS swaps the month and day.
A start and end date is chosen in the datepicher controls. This is ok, but
when "view report" is activated, RS swicthes day and month and thus it is
considered a invalid date.
A clear example is when you e.g. choose 2006-01-05 in a datepicker control.
When view reports is activated, then the date has changed to 2006-05-01.
As an extra info, the problem is userspecific, we have tried to use
different users from the same computer and the result was ok with obe user
and not ok with another. The dateformat setup was 100 % simular on these
users.
The convert function is not the answer to this problem, instead I find it to
be a really annoying bug that makes the datepicker useless.
As Michael, I really would like the solution to this problem.
Forgive my english :-)
Martin
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From you description, my understanding of this issue is: You want to
> transfer the date-time parameter in (dd/mm/yyyy) format in to the report.
> If I misunderstood your concern, please feel free to point it out.
> By default, it's not possible to set the format of the date. However I did
> a workaround for you, you can set the parameter data type to string then
> use the SQL Convert() function to convert the parameter to DateTime.
> The simple example of this will be like this.
> Select * from orders where orderDate= CONVERT(DateTime, @.mydate, 103)
>
> Here is the article about the CONVERT function.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
> ca-co_2f3o.asp
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Martin,
Thank you for your post.
Unfortunately, I could not reproduct this issue on my side. When I click
the datetime picker, it could render the date correctly.
Would you please provide some additional information about the Regional
Settings?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei
My settings are as follows:
International settings/Standards and formats = "Danish"
Short dateformat = "DD-MM-YYYY"
Dateseperator = "-"
If you have an email I would be happy to send some screensdumps.
Sincerely,
Martin
"Wei Lu" wrote:
> Hi Martin,
> Thank you for your post.
> Unfortunately, I could not reproduct this issue on my side. When I click
> the datetime picker, it could render the date correctly.
> Would you please provide some additional information about the Regional
> Settings?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Martin,
My direct email address is weilu@.ONLINE.microsoft.com (Please remove the
ONLINE before you send the email).
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu -
I notice that you respond to certain posts "welcome to MSDN managed
newsgroup support" - how can I get this same assistance? I am an MSDN
subscriber - is there some special way to post a question to get your
attention? I am wondering how I can set a default date of TODAY in RS2005
when I have a date parameter set as datetime. I want to provide a default
value of current date. Cant seem to get it working without getting a type
incorrect when I use a function like TODAY or NOW. Thanks in advance!
"Wei Lu" wrote:
> Hi Martin,
> My direct email address is weilu@.ONLINE.microsoft.com (Please remove the
> ONLINE before you send the email).
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

DateTime Format in Localized version of MSDE

Hi
How do we determine the date time format in a SQL Server instance.
Specifically I would like to know, if the date time data type in SQL Server
is Language Specific or Language Neutral.
We are facing the following problem. I have a managed app, which is
localized. I need to update some data from the managed app to the
database(we are using MSDE). When I run the managed app in Italian locale,
with Italian build of MSDE, the database update fails.
The problem we figured out was, the date time cast in database fails. This
is because the time separator(for Italian locale) in .NET app is a period,
while in SQL MSDE(Italian build) it is a colon (
The following are my queries.
1. Is Date Time data type in SQL Language specific or Language Neutral? If
it is Language Neutral, I assume it will use the en-US culture, correct me
if I am wrong.
2. If date time is language specific, how is the collation set. Is it set by
default when MSDE is installed? Will the Operating System language version,
impact the collation, while installing MSDE.
3. When I run the query 'Select GetDate()' in Query Analyzer, the time
separator is displayed as a colon. Does the language version of SQL Server
tools(query analyzer/enterprise manager) have an impact on the date time
displayed?
Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
Thanks
RT
In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
<ramjee_t@.infosys.com> writes
>Hi
>How do we determine the date time format in a SQL Server instance.
>Specifically I would like to know, if the date time data type in SQL Server
>is Language Specific or Language Neutral.
>We are facing the following problem. I have a managed app, which is
>localized. I need to update some data from the managed app to the
>database(we are using MSDE). When I run the managed app in Italian locale,
>with Italian build of MSDE, the database update fails.
>The problem we figured out was, the date time cast in database fails. This
>is because the time separator(for Italian locale) in .NET app is a period,
>while in SQL MSDE(Italian build) it is a colon (
>The following are my queries.
>1. Is Date Time data type in SQL Language specific or Language Neutral? If
>it is Language Neutral, I assume it will use the en-US culture, correct me
>if I am wrong.
Not exactly. Physically in the database it is always stored the same way
however, the collation order does determine some of the supported
formats displaying and updating a DateTime field.

>2. If date time is language specific, how is the collation set. Is it set by
>default when MSDE is installed? Will the Operating System language version,
>impact the collation, while installing MSDE.
The default collation order is set when the instance of MSDE is
installed. However under MSDE 2000 / SQL Server 2000 the collation order
of each database can be different. Thats up to you when you CREATE the
DATABASE (ie: you determine the default collation order for each
database). In addtion, you can specify the collation order to use on
each Table and Field if really required. Check BOL for the CREATE
DATABASE and TABLE. You are therefore quite capable of using the same
collation order for every instance of MSDE you install regardless of
country.

>3. When I run the query 'Select GetDate()' in Query Analyzer, the time
>separator is displayed as a colon. Does the language version of SQL Server
>tools(query analyzer/enterprise manager) have an impact on the date time
>displayed?
Its all about handling dates in a consistent manor.
Its generally a good idea to always update a DataTime field using the
universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
confused about which part is the month and day (ie: 2005-01-05 is always
5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this
also solves international differences.
Its also therefore generally a good idea to always retrieve the DateTime
in a known format. Therefore using a command like "SELECT
Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
return the date in a UK format for example. That way your application
does not get confused and the localisation to the client is left to your
application.

>Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
No Problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||Hi Andrew,
You are mixing up collation, which is a property of character type columns
and variables in SQL Server, and the language that can be set for a
connection or user. The last one determines how dates as strings are
interpreted.
1) You are right that datetime and smalldatetime in SQL Server are stored in
a binary, language-neutral format. How the datetimes are displayed depends
on the client application however. For example Query Analyzer will by
default display dates in yyyy-mm-dd hh:mm:ss format. Enterprise Manager on
the other hand will use your Windows local settings to decide the display
format. How dates as strings are interpreted when inserting, updating or
deleting depends on the language setting for the connection, which are by
default derived from the language settings for the current user, although
they can be set explicitly with SET LANGUAGE.
2) As I said earlier, collation is irrelevant for datetime. The default
language settings for the user (login) are derived from the language in
which SQL Server is installed, but can be specified explicitly when creating
the login, or changed afterwards.
3) "yyyy-mm-dd hh:nn:ss" is not a safe format for datetime. Try the
following:
SET LANGUAGE us_english
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
GO
SET LANGUAGE british
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
There are 2 safe date formats in SQL Server:
yyyymmdd
and
yyyy-mm-ddThh:mm:ss
It is _not_ a good idea to always retrieve the datetime in a known string
format. Just retrieve the datetime as datetime, and let your application and
your user decide how to display is in a human-readable format. A properly
designed application will just use the Regional Settings from Windows to
decide how to display dates, and if you return datetime in a string format,
you just end up converting datetime values twice.
Jacco Schalkwijk
SQL Server MVP
"Andrew D. Newbould" <newsgroups@.NOzadSPANsoft.com> wrote in message
news:n0wpLfBXbrpCFwsj@.zadsoft.gotadsl.co.uk...
> In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
> <ramjee_t@.infosys.com> writes
> Not exactly. Physically in the database it is always stored the same way
> however, the collation order does determine some of the supported formats
> displaying and updating a DateTime field.
>
> The default collation order is set when the instance of MSDE is installed.
> However under MSDE 2000 / SQL Server 2000 the collation order of each
> database can be different. Thats up to you when you CREATE the DATABASE
> (ie: you determine the default collation order for each database). In
> addtion, you can specify the collation order to use on each Table and
> Field if really required. Check BOL for the CREATE DATABASE and TABLE. You
> are therefore quite capable of using the same collation order for every
> instance of MSDE you install regardless of country.
>
> Its all about handling dates in a consistent manor.
> Its generally a good idea to always update a DataTime field using the
> universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
> confused about which part is the month and day (ie: 2005-01-05 is always
> 5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this also
> solves international differences.
> Its also therefore generally a good idea to always retrieve the DateTime
> in a known format. Therefore using a command like "SELECT
> Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
> return the date in a UK format for example. That way your application does
> not get confused and the localisation to the client is left to your
> application.
>
> No Problem.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com

datetime format

Hi.
I have two parameters called StartDate and EndDate.These parameters are from datetime type.
I want to view the records between these dates.I have some questions:

1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part.

2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox.
For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy

3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this?

Thanks!

Try doing a convert on your database datetime field similar to this in your query

convert(datetime, "datefield", 104)

This will format the date as dd.mm.yyyy. You can also do this on the parameter value so they are both in the same format. Your query would look something like this:

select * from table where convert(datetime, "datefield", 104) >= convert(datetime, @.StartDate, 104) and convert(datetime, "datefield", 104) <= convert(datetime, @.EndDate,104)

To display all records you can set the default values to the maximum and minimum dates in your database. The issue with this is that everytime the report is opened, it will automatically run for all dates. Not sure how to make it work only if the user doesn't select dates.

|||kmcclung thanks for the reply.
But it didn't work.
I wrote convert(datetime, myDateField, 104) and then tried the third parameter for 103, 4, ...
But it didn't change.
Then I realized that it is not dependent on that number.
It uses only the default datetime format.
The records in my database are like dd.mm.yyyy hh:mm:ss
And after I used the CONVERT function NOTHING changed.
I only want the date part to be visible.(only want this)
And the second problem is that as I said before when I click the calendar button near the date texbox area and select a date like 15.12.2001 then it is written to textbox like 12.15.2001.
And because of not existing a month number like 15 an error occurs.
I mean that I want to change that calendar's format.

How can I correct this?|||What type of database you are using?|||

0) It sounds like your database is NOT storing dates with a DATETIME format. Why not?

1) To take '15.11.1984 23:59:14' and store it as a DATETIME with time stripped off (set to midnite):

CONVERT(DATETIME, CAST(CONVERT(DATETIME, '15.11.1984 23:59:14', 104) AS INT))

datetime field overflow

DATETIME data type can hold a date between January 1, 1753 and December 31,
9999. SMALLDATETIME can hold a date between January 1, 1900 and June 6,
2079. Trying to set a DATETIME column outside of these ranges will cause an
error.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:exDd7YkkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> Why would i get a "datetime field overflow" error ? Under what conditions
> ?
> Thanks
>Why would i get a "datetime field overflow" error ? Under what conditions ?
Thanks|||DATETIME data type can hold a date between January 1, 1753 and December 31,
9999. SMALLDATETIME can hold a date between January 1, 1900 and June 6,
2079. Trying to set a DATETIME column outside of these ranges will cause an
error.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:exDd7YkkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> Why would i get a "datetime field overflow" error ? Under what conditions
> ?
> Thanks
>

DATETIME field

I have a CreatedDate column of type datetime. I am trying to query for
CreatedDate = somedate, but not results are returned. I understand this is
because the time is also included in the data will never equal a specific
date. BOL recommends doing a string comparison using LIKE; however, it
doesn't return results either. Any suggestions?
WBWB,
What is the datatype somedate? Try CONVERTing CreatedDate to a format that
doesn't include the time:
SELECT CreatedDate FROM YourTable
WHERE CONVERT(char(8), CreatedDate, 112) = somedate
-Andy
"WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
>I have a CreatedDate column of type datetime. I am trying to query for
> CreatedDate = somedate, but not results are returned. I understand this
> is
> because the time is also included in the data will never equal a specific
> date. BOL recommends doing a string comparison using LIKE; however, it
> doesn't return results either. Any suggestions?
> WB
>|||somedate is usually an entry like '04/12/2005'
"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:%23dR1D84PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> WB,
> What is the datatype somedate? Try CONVERTing CreatedDate to a format
that
> doesn't include the time:
> SELECT CreatedDate FROM YourTable
> WHERE CONVERT(char(8), CreatedDate, 112) = somedate
> -Andy
> "WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
specific
>|||> somedate is usually an entry like '04/12/2005'
But is that April 12th or December 4th? You can Google this group and find
tons of dicussions on this topic.
Good luck.
-Andy|||Try type 101 instead of 112, although it should work either way.
"WB" <none> wrote in message news:eUren%234PFHA.2788@.TK2MSFTNGP09.phx.gbl...
> somedate is usually an entry like '04/12/2005'
> "Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
> news:%23dR1D84PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> that
> specific
>|||> BOL recommends doing a string comparison using LIKE;
BOL is WRONG, in my opinion.
If you want to find values for today, use a range query:
DECLARE @.dt SMALLDATETIME
SET @.dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT columns FROM table
WHERE dateColumn >= @.dt
AND dateColumn < (@.dt + 1)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
however, it
> doesn't return results either. Any suggestions?
> WB
>|||I suggest you 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/
"WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
>I have a CreatedDate column of type datetime. I am trying to query for
> CreatedDate = somedate, but not results are returned. I understand this i
s
> because the time is also included in the data will never equal a specific
> date. BOL recommends doing a string comparison using LIKE; however, it
> doesn't return results either. Any suggestions?
> WB
>

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 data type resulted in an out-of-range datetime value. Please help

Hi,

I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date

'31/08/2006 23:28:59'

I get the error "...datetime data type resulted in an out-of-range datetime value"

I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?

cheers,

Ernest

I guess itis caused by the date format in SQL Server. Please try following statements:

set DATEFORMAT dmy
declare @.t smalldatetime
set @.t='31/08/2006 23:28:59'
select @.t

|||

Thanks Lori,

It appears that when I use parameters in my SqlCommand object this works like a treat. God bless the parameters!!

DateTime data type and 12:00am

Hi guys.

I have a datetime data type column set up for keeping dates. 12:00am would like to hang around when I don't want it too. I don't know if the best solution is to have the server format it for me or if that should be done on the client side. In either case I need a little guidance. My app is a simple blog program that uses a dataset to populate a datalist from mssql 2005. I've looked through some of the other entries that people have posted but I'm to new to this particular issue and sql to transcribe their issue's fix to mine... at least from the enties I've read so far. hence my requst for help! Your assistance is greatly appreciated.

Mucho thanks.

Fatthippo.

You can format the datetime either way, but it would be better by doing it from client side. For example,

<ItemTemplate>dob:<asp:Label ID="dobLabel" runat="server" Text='<%# Bind("dob","{0:MM/dd/yyyy}")%>'></asp:Label></ItemTemplate>

|||

Thankyou limno!

that satifies the questions but if I'm always formatting what comes out of mssql and not what's going in, will that hinder any search querries I might want to do in the future if 12:00am is always at the end? In otherwords, is there a benfit or downside to using the technique in the above example?

Thanks again!

Fatthippo

|||

Datetime data type has two parts date and time. It should be a good practice to use datetime this way instead of as a string type. From my limit knowledge, we should choose to do this sort of formating from client side to save a little bit extral calculation on database engine. There are other ways to format date time to fit your need. You can look it up depending on what kind of control you are using. When I am working on my projects, I use them interchangably in light load applications. But without further testing, I cannot give you any firm recomendation on this. You can search for this information from various forums and I am sure you will get a lot of information on this. I like to play with formating datetime in SQL to learn.

Datetime data type ?

I have a table in the database. One of the column is of datetime type.

What is the best way to insert and retrieve value from that column. Here is the scenario

1) I want to insert value '02/03/2006' into the column. How does the sql server know that the month is 02 and not 03. Will it look into the system settings. If so then can I sepcify custom format to distinguish between month,day and year.

2) I want to retrieve value from the datetime column in the format dd/mm/yyyy hh:mm:ss AM/PM . What sql statement I have to use??



ThanksI belive SQL uses the language setting to determine how you are entering the date, so when you are using the English language and Date is setup at 2/3/06 it assume mm/dd/yyyy. To retrun the date time value you want to return try using this:

Select Convert(varchar(20),getdate(),109). To get the AM/PM setup try this website, http://sqladvice.com/blogs/repeatableread/archive/2005/02/22/4261.aspx
They have a function already written to do that.|||1. SET DATEFORMAT (http://msdn2.microsoft.com/en-us/library/ms189491.aspx)

2. The SQL Statement should only retrieve the data, not format it. The workstation client software ought to control the formatting. If you really want to cause yourself frustration, periodically reformat your boot drive... That will waste about as much time as formatting data on the SQL Server, and will be more obvious in how it wastes that time... If you prefer the subtle approach to wasting time, you can use the SQL Convert (http://msdn2.microsoft.com/en-us/library/ms187928.aspx) function to happily waste many hours!

-PatP|||The SQL Statement should only retrieve the data, not format it. The workstation client software ought to control the formatting. If you really want to cause yourself frustration, periodically reformat your boot drive... That will waste about as much time as formatting data on the SQL Server, and will be more obvious in how it wastes that time... If you prefer the subtle approach to wasting time, you can use the SQL Convert (http://msdn2.microsoft.com/en-us/library/ms187928.aspx) function to happily waste many hours!

-PatPHad your coffee yet this morning, Mr. Sunshine?|||Had your coffee yet this morning, Mr. Sunshine?Oh, can't you just see me jazzed on caffine too? Egad!

-PatP|||Web2000:

In case it wasn't obvious from the silly description, I was trying to inject a bit of humor into the description of a very bad idea...

While it is possible to format your data using SQL Server, I've never seen a case where it was a good idea. This actually makes it almost impossible to handle locale dependant issues, which in turn makes your code almost impossible to scale. If you handle the formatting at the client (even if that is a web browser), you can decide on a client-by-client basis how to handle the formatting (which is typically done for you with zero effort on your part), and you distribute the workload involved in that formatting. It is a win/win situation for you as a developer.

-PatP|||One format that never fails is YYYY-MM-DD, no matter what are your regional settings, SQL Server always will correctly parse and uses a date formated in that way.

Datetime data type ?

I have a table in the database. One of the column is of datetime type.

What is the best way to insert and retrieve value from that column. Here is the scenario

1) I want to insert value '02/03/2006' into the column. How does the sql server know that the month is 02 and not 03. Will it look into the system settings. If so then can I sepcify custom format to distinguish between month,day and year.

2) I want to retrieve value from the datetime column in the format dd/mm/yyyy hh:mm:ss AM/PM . What sql statement I have to use?

Thanks

This is a very good question, one that many forgets to ask (until it's too late) =;o)

1) This is pretty easy. The answer is to use another format, one that cannot be misunderstood regardless of language or date settings. The most common is ssyymmdd (eg 20060203 for february third this year)

You can find an excellent article on the subject here.

How do I delimit/format dates for database entry?
http://www.aspfaq.com/show.asp?id=2023

2) The display of dates are controlled by converting the datetime to a string and supplying a style parameter for the output you wish to have. These can be found in BOL under the pargraph that discusses 'CONVERT'. At a quick glance it doesn't look like there's one that exactly matches what you want, but it's possible to use different parts and concatenate them together in order to make a 'custom' format.

select convert(char(10), getdate(), 103) + ' ' + convert(char(8), getdate(), 108)

.. is close, but the time is in 24hr format instead of AM/PM.

If you really must have AM/PM and a 12hr clock, there are formats that one could use, but the code to rip out those pieces would be a bit more complex.

/Kenneth

DateTime Data Type

Hi,
I am new to SQL Server, I am using SQL Server 2000, I want to store only
date (not time) in my table, is it possible in sql , is there any data types
except datetime and smalldatetime.
Thanks in advance.
Hardik Shah.Hardik Shah wrote:
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store
> only date (not time) in my table, is it possible in sql , is there
> any data types except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
No, you need to store a time portion as well. You can set it to 12am if
you want. Use a smalldatatime if you don't require full time precision
as it's only 4 bytes instead of 8.
David Gugick
Imceda Software
www.imceda.com|||No. Sql2k does not have date only datatype.
Here is Tibor's excellent article on the subject. I would suggest you take a
quick look.
http://www.karaszi.com/sqlserver/info_datetime.asp
-oj
"Hardik Shah" <har_sha_99@.hotmail.com> wrote in message
news:OvHsm9NTFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store only
> date (not time) in my table, is it possible in sql , is there any data
> types
> except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
>|||No and no to both questions.
When you write a date to the column, you can save it as so:
'20050429'
The time part will be '00:00:00.000'
So a query: ...where dateColumn = '20050429'
will return that row.
You must be coming from Access, right?
"Hardik Shah" <har_sha_99@.hotmail.com> wrote in message
news:OvHsm9NTFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store only
> date (not time) in my table, is it possible in sql , is there any data
> types
> except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
>|||By default, no. However, there are numerous tricks to accomplish the same go
al.
1. Use a smallInt instead of a datetime or smalldatetime. That simply means
you
have to do a bunch of casting back and forth to get data out of the table.
Another downside to this approach is that it is not obvious to other develop
ers
that this is what you are doing.
2. Use a datetime/smalldatetime and put a check constraint on the table that
requires all values have zero for the time element. That would require that
the
client code strip the time portion before it passes it to the database lest
it
get an error.
3. Use a datetime/smalldatetime and put an Instead Of trigger on the table t
hat
strips the time portion before the value is entered.
4. Let the time portion be stored, but ignore it or strip it for purposes of
retrieval. It does mean you have to be a bit more careful with your queries,
but
it obviously simplifies table structure. In addition, if you are following a
modular design and have all your database calls in a central location, you c
an
strip the time portion in your database calls.
HTH
Thomas
"Hardik Shah" <har_sha_99@.hotmail.com> wrote in message
news:OvHsm9NTFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am new to SQL Server, I am using SQL Server 2000, I want to store only
> date (not time) in my table, is it possible in sql , is there any data typ
es
> except datetime and smalldatetime.
>
> Thanks in advance.
>
> Hardik Shah.
>