Date Query Plz Help

Hello All
I am stuck in silly point Please help me and excuse me for a bad
Knowledege
of SQL store Procedurte Programming.
My date are store in DB as 10/17/2005 5:37:07 PM
How Do I fetch the Name of month from this.
Thanking You
With regards
Tarun Sinhaselect datename(month,getdate())
HTH. Ryan
<tarun.sinha@.gmail.com> wrote in message
news:1143020683.081697.80810@.i39g2000cwa.googlegroups.com...
> Hello All
> I am stuck in silly point Please help me and excuse me for a bad
> Knowledege
> of SQL store Procedurte Programming.
> My date are store in DB as 10/17/2005 5:37:07 PM
> How Do I fetch the Name of month from this.
>
> Thanking You
> With regards
> Tarun Sinha
>|||Hello Dear
Thanks for replying me so soon
I have a number of Date in DB let say,
Code Userid AssigningDate
fjjfjsddj 14 10/17/2005 5:37:07 PM
fjjfjsddj 14 10/17/2005 5:37:07 PM
fjjfjsddj 14 11/17/2005 5:37:07 PM
fjjfjsddj 14 11/17/2005 5:37:07 PM
fjjfjsddj 14 11/17/2005 5:37:07 PM
fjjfjsddj 12 9/17/2005 5:37:07 PM
fjjfjsddj 11 9/17/2005 5:37:07 PM
Now I want to Fetch all Those User Whose falls on same Month.
and also want to Know the Name of Month.
very Obiligied for You
With regards
Tarun Sinha|||select DATENAME(month,AssigningDate), * from tablename
WHERE DATENAME(month,AssigningDate) = 'March'
HTH. Ryan
<tarun.sinha@.gmail.com> wrote in message
news:1143021721.983590.131880@.t31g2000cwb.googlegroups.com...
> Hello Dear
>
> Thanks for replying me so soon
> I have a number of Date in DB let say,
> Code Userid AssigningDate
> fjjfjsddj 14 10/17/2005 5:37:07 PM
> fjjfjsddj 14 10/17/2005 5:37:07 PM
> fjjfjsddj 14 11/17/2005 5:37:07 PM
> fjjfjsddj 14 11/17/2005 5:37:07 PM
> fjjfjsddj 14 11/17/2005 5:37:07 PM
> fjjfjsddj 12 9/17/2005 5:37:07 PM
> fjjfjsddj 11 9/17/2005 5:37:07 PM
>
> Now I want to Fetch all Those User Whose falls on same Month.
> and also want to Know the Name of Month.
>
> very Obiligied for You
> With regards
> Tarun Sinha
>|||Thank You dear
You Help me a lot ..
With regards
Tarun Sinha

date prolem

I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect o
f
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function
.
John
"Rajani" wrote:

> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dat
es
> in dd-mon-yyyy?
> thanks in advance|||
> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...[vbcol=seagreen]
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>|||Ooopps!
"Uri Dimant" wrote:

>
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
>
>sql

date prolem

I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect of
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dates
> in dd-mon-yyyy?
> thanks in advance|||> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>> I have created a table in which one column is datetime data type. But its
>> storing date andtimetogether. How can i chane the format to store only
>> dates
>> in dd-mon-yyyy?
>> thanks in advance|||Ooopps!
"Uri Dimant" wrote:
> > To expand on Hugo's post.
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> > Hi
> >
> > To expand on Hugo's post. The SQL Server datetime datatype will hold both
> > a
> > date and time. If you insert a date value with no time portion then the
> > time
> > will be defaulted to 00:00:00.000 and if you specify only a time the date
> > is
> > defaulted to
> > 1900-01-01. With SQL Server there is the CONVERT function that will format
> > a
> > datetime datetype value as a string. This will take a format specifier to
> > determine what format the string is. The getdate function will return the
> > current date and time, Hugo's second INSERT statement will use the FLOOR
> > function to truncate the time portion of a datetime data value, the effect
> > of
> > adding 1 to getdate will add one day. Therefore his code will add one day
> > to
> > the current date and time and then truncate the time portion.
> >
> > See Books Online for more on the datetime data type and the CONVERT
> > function.
> >
> > John
> >
> >
> > "Rajani" wrote:
> >
> >> I have created a table in which one column is datetime data type. But its
> >> storing date andtimetogether. How can i chane the format to store only
> >> dates
> >> in dd-mon-yyyy?
> >>
> >> thanks in advance
>
>

Date problem in SQL Server

Hi

Good day!

I am having some problem with the date-time datatype. I want to store only date in my table in this format - DD-MM-YYYY - and I do not need time. This must be compatible with the format of Visual Basic.

How to achieve this?

SQL Server datetime datatype stores both DATE and TIME. [datetime] has a presicion to milliseconds, and [shortdatetime] has a precision to minutes.

If you do NOT provide the TIME component, it will default to midnight (or 12:00:00 AM). It is very common for applications to ignore the time component. If you do not need the TIME component, use a shortdatetime datatype.

Refer to Books Online about [datetime] and [shortdatetime] datatypes.

|||

what is the problem in storing date and time. is it because of Storage size or is it because u wnat to show only date to the user. If you want to show only date.... this formating can be done at the frontend. Don't change the datatype... you store the data as datetime only and do the formating at the frontend. And also check CONVERT/CAST function in BOL.

Madhu

|||

You cannot do this with SQL Server 2005 and previous release. In Katmai (next release), we define several new datetime data types and you will be able to do it.

Or you can use the techniques mentioned by the previous posts.

Thanks.

|||

Hi Madhu

My problem is actually in queiry. When I use the type date/time and I assign a default value to GETDATE() in sql server, it stores both date and time. But when I run sproc with where clause like - WHERE Date = @.Date - where the time is not given, I can't find the date!

How to solve it? I am using VB to run the sproc.

Regards

Kapalic

|||

in this case you may need to use CONVERT Function of SQL Server. Suppose u r passing date mm/dd/yyyy then

Where convert(varchar(10),Date,101)='04-10-2007'

Madhu

|||

That is because when you are using @.Date without a time portion, it defaults to midnight (12:00:00.000), and you don't have any rows with that exact time.

Try the following WHERE clause:

Code Snippet

WHERE ( [Date] >= @.Date

AND [Date] < ( dateadd( day, 1, @.Date )

)

This assumes that @.Date is in the form of 'mm/dd/yyyy', and that you want all values from midnight until the last possible moment before the next midnight (Or [ >= 12:00:00.000 ] AND [ < 11:59:59.997 ]

(You don't really want to follow Madhu's suggestion of using convert(varchar(10), Date, 101) BECAUSE it would invalidate the use of an index and could be very inefficient.)

|||

Hi Arnie,

Thnk you very much for your reply. The problem is, I want to use the date format as DD/MM/YYYY. This I forgot to mention. And I want to link the sproc with a DateTimePicker control in Visual Basic.

Please resque...

Regards

Kapalic

|||

Read about CONVERT Function in BOL ... what u need to do is ... convert the date in sql to which ever format u required. the below statement will do that

if the supplied date is 04/10/2007 dd/mm/yyyy run the below mentioned statement and see teh result

Where convert(varchar(10),Date,105)='04-10-2007'

Madhu

|||

Using a VB date format of 'DD/MM/YYYY', and using a DateTimePicker is NOT a problem as long as you are using a Date datatype in VB, and the procedure call is using a SQL Datetime datatype.

If your VB date variable it used when you make the SQL Stored procedure call, the WHERE clause example above will work just fine.

Date Format

Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
rather than the default MM/dd/yyyy.
Thanks in anticipation,
Paul BeckettPaul,
The date is stored in neither format, but as a binary number. What you are
seeing is the way the date is interpreted to and from a character string.
This is affected by the language and locale that you run with. See SET
LANGUAGE.
Also, note Aaron Bertrand's message just a little earlier in this group,
which I quote in part: " I use the ISO standard, unambiguous, and SQL
Server safe YYYYMMDD format."
If you want SQL Server to output a string format that is different from your
machine settings, examine the CONVERT command, which has several formatting
options for dates.
Russell Fields
"Paul Beckett" <paul_beckett@.lineone.net> wrote in message
news:c15tsk$ikf$1@.titan.btinternet.com...
> Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
> rather than the default MM/dd/yyyy.
> Thanks in anticipation,
> Paul Beckett
>|||SQLServer doesn't store formatting information with a DATETIME or
SMALLDATETIME column. The date is actually represented internally as two
integers but it is the job of the client application to pass dates to the
server in a valid format and then to format the output for display.
When passing dates to a DATETIME/SMALLDATETIME column in SQLServer 2000, use
one of the following string formats:
'20031231' -- Just the date
'2003-12-31T17:59:00' -- Date/hours/minutes/seconds
'2003-12-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
these are guaranteed to work regardless of regional date format settings on
the server. Although it's possible to use SET DATEFORMAT to allow other
formats it's safer to stick to one of the standard alternatives shown above.
Format the date for display at the client side. Or use CONVERT(VARCHAR, ...,
103) to turn your dates into a VARCHAR if you must do it at the server.
--
David Portas
SQL Server MVP
--

Date Format

Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
rather than the default MM/dd/yyyy.
Thanks in anticipation,
Paul BeckettPaul,
The date is stored in neither format, but as a binary number. What you are
seeing is the way the date is interpreted to and from a character string.
This is affected by the language and locale that you run with. See SET
LANGUAGE.
Also, note Aaron Bertrand's message just a little earlier in this group,
which I quote in part: " I use the ISO standard, unambiguous, and SQL
Server safe YYYYMMDD format."
If you want SQL Server to output a string format that is different from your
machine settings, examine the CONVERT command, which has several formatting
options for dates.
Russell Fields
"Paul Beckett" <paul_beckett@.lineone.net> wrote in message
news:c15tsk$ikf$1@.titan.btinternet.com...
> Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
> rather than the default MM/dd/yyyy.
> Thanks in anticipation,
> Paul Beckett
>|||SQLServer doesn't store formatting information with a DATETIME or
SMALLDATETIME column. The date is actually represented internally as two
integers but it is the job of the client application to pass dates to the
server in a valid format and then to format the output for display.
When passing dates to a DATETIME/SMALLDATETIME column in SQLServer 2000, use
one of the following string formats:
'20031231' -- Just the date
'2003-12-31T17:59:00' -- Date/hours/minutes/seconds
'2003-12-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
these are guaranteed to work regardless of regional date format settings on
the server. Although it's possible to use SET DATEFORMAT to allow other
formats it's safer to stick to one of the standard alternatives shown above.
Format the date for display at the client side. Or use CONVERT(VARCHAR, ...,
103) to turn your dates into a VARCHAR if you must do it at the server.
David Portas
SQL Server MVP
--

Date datatype

I need to store older dates than Jan 1 1753. What date type should I use? I
need to store for example 26 August 1537.
With thanks
Hi
Probably you will need to store it as VARCHAR(n) column.
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks
|||Hi Edward,
You can go with Varchar data type.
Thanks
Hari
SQL Server MVP
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
>I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks
|||That means that the column would have not "date" abilities.
"Uri Dimant" wrote:

> Hi
> Probably you will need to store it as VARCHAR(n) column.
> "EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
> news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I
>
>
|||What "date abilities" do you want? For ancient dates it's hard to do
any of the things we take for granted with DATETIME (validation,
comparison, sorting, date arithmetic). I expect you know that our
Gregorian Calendar didn't exist in 1537 so what calendar logic would
you expect to apply to such a date? You have to design them yourself
I'm afraid because SQL Server has no way of knowing what calendar was
in use in your particular part of the world on any given date.
David Portas
SQL Server MVP
|||Hi
Look at :
http://codebetter.com/blogs/raymond.../01/34519.aspx
http://groups.google.ch/group/micros... 7fa92fdeea5a
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"EdwardH" wrote:

> I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks

Date datatype

I need to store older dates than Jan 1 1753. What date type should I use? I
need to store for example 26 August 1537.
With thanksHi
Probably you will need to store it as VARCHAR(n) column.
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks|||Hi Edward,
You can go with Varchar data type.
Thanks
Hari
SQL Server MVP
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
>I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks|||That means that the column would have not "date" abilities.
"Uri Dimant" wrote:
> Hi
> Probably you will need to store it as VARCHAR(n) column.
> "EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
> news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> > I need to store older dates than Jan 1 1753. What date type should I use?
> I
> > need to store for example 26 August 1537.
> > With thanks
>
>|||What "date abilities" do you want? For ancient dates it's hard to do
any of the things we take for granted with DATETIME (validation,
comparison, sorting, date arithmetic). I expect you know that our
Gregorian Calendar didn't exist in 1537 so what calendar logic would
you expect to apply to such a date? You have to design them yourself
I'm afraid because SQL Server has no way of knowing what calendar was
in use in your particular part of the world on any given date.
--
David Portas
SQL Server MVP
--|||Hi
Look at :
http://codebetter.com/blogs/raymond.lewallen/archive/2004/12/01/34519.aspx
http://groups.google.ch/group/microsoft.public.sqlserver.server/browse_thread/thread/9b6613e416ae827c/55b87fa92fdeea5a?q=sql+date+1753&rnum=2&hl=en#55b87fa92fdeea5a
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"EdwardH" wrote:
> I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks

Date datatype

I need to store older dates than Jan 1 1753. What date type should I use? I
need to store for example 26 August 1537.
With thanksHi
Probably you will need to store it as VARCHAR(n) column.
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks|||Hi Edward,
You can go with Varchar data type.
Thanks
Hari
SQL Server MVP
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
>I need to store older dates than Jan 1 1753. What date type should I use? I
> need to store for example 26 August 1537.
> With thanks|||That means that the column would have not "date" abilities.
"Uri Dimant" wrote:

> Hi
> Probably you will need to store it as VARCHAR(n) column.
> "EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
> news:55BDB48D-B01A-4075-815D-C139E45E9CF8@.microsoft.com...
> I
>
>|||What "date abilities" do you want? For ancient dates it's hard to do
any of the things we take for granted with DATETIME (validation,
comparison, sorting, date arithmetic). I expect you know that our
Gregorian Calendar didn't exist in 1537 so what calendar logic would
you expect to apply to such a date? You have to design them yourself
I'm afraid because SQL Server has no way of knowing what calendar was
in use in your particular part of the world on any given date.
David Portas
SQL Server MVP
--|||Hi
Look at :
http://codebetter.com/blogs/raymond...2/01/34519.aspx
http://groups.google.ch/group/micro...fdeea5a

--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"EdwardH" wrote:

> I need to store older dates than Jan 1 1753. What date type should I use?
I
> need to store for example 26 August 1537.
> With thanks

Date Conversion Problem

Hi,

I've got a table with the columns DAY, MONTH and YEAR, all of them are numeric. I want to join them and store in a single datetime column.

I've been trying several ways to do that, but unfortunately I receive the same error :

"[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Probably I don't know how to use the CONVERT/CAST function properly.

Any ideas?

(Thanx)No, this error normally means that you are trying to convert values that are not an actual date.

11/31/03, or 2/29/2003, or 13/05/04, for example.

This happens when you have a database that does not store values in datetime format. It can also happen if you supply a day, month, and year value to SQL server in 2-digit formats and SQL server is unsure which value represents which part of the the date.

What code are you using for your conversion? You could wrap the ISDATE() function around it and query against your source table to find any values that it cannot convert.|||CAST(columnMonth AS Char(2)) + '/' + CAST(columnDay AS Char(2)) + '/' + CAST(columnYear AS Char(4))

returns the string which can be inserted into the field with DATETIME datatype|||STR(columnMonth, 2) + '/' + STR(columnDay, 2) + '/' + STR(columnYear, 4)

should work too.

DATE AND HOUR

Hello people,

I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this:

UPDATE S_ACC_MONEY
SET Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108)

Does anyone knows a better way to do that? Any other comments?

Thanks!

you might also want to do a final conversion to datetime as in:

Date_Oper =CONVERT(datetime,CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108))

Date / Time Field

Dear Frends
Iam using SQL Server 2000
Is there any way to use Date time field to store value before the Date 01-Jan-1753?
ThanksYes. Go to Books Online and, at the index tab, type in "two digit year cutoff option". It tells you how to change it there.|||I tray to Configure SQL server using the above "tow degits year cutoff" option to go before the year 1753, But I can't
Could you please help me

Thanks|||Give me a walkthrough of how you tried to change it, including script if you used one.|||I don't think you can store a date before 1 Jan 1753 in SQL Server. The DATETIME datatype only stores "when", but in order to correctly store dates before the Gregorian calendar reformation you also need to know "where" the date was recorded. This is because dates like June 1, 1750 were observed over a two week period depending on where it was observed, for example it was observed much earlier in France than in England.

For more details on this problem, see the article in the Wikiedia (http://en.wikipedia.org/wiki/Gregorian_Calendar).

-PatP|||I have tried the following in SQL Query Analiser (Master DB)

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'two digit year cutoff', 1751
GO
RECONFIGURE
GO

it gives the message
'1751' is not a valid value for configuration option 'two digit year cutoff'.

Thank you|||You can't do it at all. I just did a little test to confirm. Even if you do get it to recognize earlier years, when you try to input a date earlier than 1753, you will get a char conversion error. I would be happy to learn something different from MS, but I don't believe what you are wanting to do is possible at all with datetime.

This is validated by Books Online. See below quote:

Remarks
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Date

Hi,
i store dates in a smalldatetime field. Now i want to retrieves all records
between 2 dates
SELECT * FROM myTable WHERE addDate BETWEEN '2005/7/24' AND '2005/7/25'
(suppose to retrieve all records added yesterday and today.)
I get no error but get no results back ?
What do i wrong ?
Grard.I discover when i add the time it works. How can i work only with the date ?|||Don't use BETWEEN, and use a sensible and unbambiguous date format.
WHERE addDate >= '20050724' AND addDate < '20050726'
For more info, see
http://www.aspfaq.com/2280
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:iz9Fe.153365$LX6.8091701@.phobos.telenet-ops.be...
> Hi,
> i store dates in a smalldatetime field. Now i want to retrieves all
> records between 2 dates
> SELECT * FROM myTable WHERE addDate BETWEEN '2005/7/24' AND '2005/7/25'
> (suppose to retrieve all records added yesterday and today.)
> I get no error but get no results back ?
> What do i wrong ?
> Grard.
>
>|||You may need to allow for addDate having times other than midnight. Also,
use a safe date format rather than rely on SQL Server interpreting your
local date format correctly:
SELECT *
FROM myTable
WHERE addDate >= '20050724'
AND addDate < '20050725' ;
David Portas
SQL Server MVP
--|||CORRECTION:
SELECT *
FROM myTable
WHERE addDate >= '20050724'
AND addDate < '200507256' ;
David Portas
SQL Server MVP
--|||Ok, now i understand difference with Access. Thx everybody. Gerard