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 Issue!

I am storing dates in SQL Server 2000 (Using ASP).
The date is getting validated against the Ymd format, by using SET DATEFORMA
T.
In the Enterprise Manager - the date is displayed in local format (dmY).
When i run a store procedure manually it is returned as Ymd.
When i execute stored procedure from asp and load data into a recordset it i
s
displayed in mdY Format.
This is obviously quite confusing. Can anyone give me some insight into what
is happening. How i can retrieve a date in one format (Ymd).Hi
Seems u have to format date using FORMAT Function
renjith
"AJ" wrote:

> I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET DATEFOR
MAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into wh
at
> is happening. How i can retrieve a date in one format (Ymd).|||I dont know which Function you mean with FORMAT, but here is another option
for that. A good pratice for me is to get the data back in a very common
format and to format it at the client side. The settings the date and time
is formatted depends on some settings concerning the client, the User
connnction to SQL Server (with its special user localized settings) or the
server settings. Perhaps you should read here a little bit further:
http://www.karaszi.com/SQLServer/in...p#OutputFormats
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A2A764AE-4BD0-4B0F-B5FF-A9FF40B6060F@.microsoft.com...
> Hi
> Seems u have to format date using FORMAT Function
> renjith
> "AJ" wrote:
>|||Hi
since AJ is using ASP , there is format function in VB for formatting data
so tht he can give as Format(DateColumn, "Ymd").
Renjith
"Jens Sü?meyer" wrote:

> I don′t know which Function you mean with FORMAT, but here is another opt
ion
> for that. A good pratice for me is to get the data back in a very common
> format and to format it at the client side. The settings the date and time
> is formatted depends on some settings concerning the client, the User
> connnction to SQL Server (with it′s special user localized settings) or t
he
> server settings. Perhaps you should read here a little bit further:
> http://www.karaszi.com/SQLServer/in...p#OutputFormats
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A2A764AE-4BD0-4B0F-B5FF-A9FF40B6060F@.microsoft.com...
>
>|||OK, that makes sense, i just thought you mean th non-existing function
FORMAT for TSQL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Renjith" <Renjith@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6F1D14DE-AB4F-4CE9-B7BE-6FDC5C1772EC@.microsoft.com...
> Hi
> since AJ is using ASP , there is format function in VB for formatting data
> so tht he can give as Format(DateColumn, "Ymd").
> Renjith
>
> "Jens Smeyer" wrote:
>|||The regional settings on your computer influence how the date is presented
in Enterprise Manager. So if the regional settings are British Enterprise
Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
influenced by the regional settings, and will always display dates in
yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.
However, how SQL Server interprets the dateformat of strings depends on the
settings for your login in SQL Server. In Enterprise Manager, look under
<Server>\Security\Logins and look at the Default Language for your username
(or BUILTIN\Administrators if you are a Windows administrator on your local
machine).
To avoid problems with dates as strings, use the formats that are always
interpreted the same by SQL Server independent of any settings:
yyyymmdd and
yyyy-mm-ddThh:mm:ss
Jacco Schalkwijk
SQL Server MVP
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:FCADD866-6062-4125-9338-4C378973487B@.microsoft.com...
>I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET
> DATEFORMAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
> is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into
> what
> is happening. How i can retrieve a date in one format (Ymd).|||> Query Analyzer isn't
> influenced by the regional settings
... unless you check Tools, Options, Connections, "Use regional settings wh
en displaying ... dates
and times". :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:eZ5Z1z3XFHA.3040@.TK2MSFTNGP14.phx.gbl...
> The regional settings on your computer influence how the date is presented
> in Enterprise Manager. So if the regional settings are British Enterprise
> Manager will _display_ the dates in dd/mm/yyyy. Query Analyzer isn't
> influenced by the regional settings, and will always display dates in
> yyyy-mm-dd hh:mm:ss, which is known as the ODBC canonical dateformat.
> However, how SQL Server interprets the dateformat of strings depends on th
e
> settings for your login in SQL Server. In Enterprise Manager, look under
> <Server>\Security\Logins and look at the Default Language for your usernam
e
> (or BUILTIN\Administrators if you are a Windows administrator on your loca
l
> machine).
> To avoid problems with dates as strings, use the formats that are always
> interpreted the same by SQL Server independent of any settings:
> yyyymmdd and
> yyyy-mm-ddThh:mm:ss
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AJ" <AJ@.discussions.microsoft.com> wrote in message
> news:FCADD866-6062-4125-9338-4C378973487B@.microsoft.com...
>|||You need to change the query in all enviornments to use the convert date
standard formats.
select convert(nvarchar,getdate(),101)
Which returns in mm/dd/yyyy format.
05/23/2005
Refer to books online and search for CAST and CONVERT as search criteria and
you will find the list of output standards.
"AJ" wrote:

> I am storing dates in SQL Server 2000 (Using ASP).
> The date is getting validated against the Ymd format, by using SET DATEFOR
MAT.
> In the Enterprise Manager - the date is displayed in local format (dmY).
> When i run a store procedure manually it is returned as Ymd.
> When i execute stored procedure from asp and load data into a recordset it
is
> displayed in mdY Format.
> This is obviously quite confusing. Can anyone give me some insight into wh
at
> is happening. How i can retrieve a date in one format (Ymd).

Date is not getting saved in propper manner

hi friends

In my form i am storing system's date in the database.

4 that i m using a variable of date like:

dim today as new date

today=now.date

but its storing the default date that is '1/1/1900'

how to overcome this problem?

Hello:

Can you show us some of your code using to perform so?

Regards

|||

Hey,

If you are storing the date for when the row was created, you could set a default value for the date field with the default value of getdate(); this will get the date from the system time. Otherwise, try DateTime.Today instead of using a date variable. From that, you can then use ToString() to convert it to a string, or whatever you want to do with it.

Date from Date/Time Field

I have what I'm assuming is an easy question to answer, but I haven't found the answer yet.

I am storing date and time info in a SmallDateTime field. The problem is when I try to retrieve records for a specific date.

For example,

"SELECT field_date FROM table WHERE field_date = '4/15/2002';"

This SQL statement returns no data. I'm assuming it's looking for an exact match, which it won't find due to the time info.

I tried to use LIKE, but SQL Server didn't like LIKE.

How do I retrieve records by date only?

Thanks.three options:

store the dates without time, essentialy making the time part midnight.

convert "field_date" to a string, this can be slow as you will scan the entire table.

change the where caluse to search using between "where filed_date between '4/15/2002' and '4/15/2002 23:59:59:999'"

date formatting

hi
below is my query AdditionalField3 is varchar datatype .and in this field my date is
storing like this 12/05/06..now i wanted to change format of this field AdditionalField3
like 2006-12-5

how to do this?
SELECT *
FROM Tbl_CMS_UploadDetails
WHERE AdditionalField3 = '2006-12-5'

You can use the following statement..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Cast(AdditionalField3 as datetime) = Cast('2006-12-5' as datetime)

|||

i m getting below error

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

|||Ok.. What is your date format 12/05/06|||yes and that field is varchar|||

Ok..

If you use dd/mm/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 103) = Cast('2006-12-5' as datetime)

if you use mm/dd/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime)

|||

i tried both queries getting error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||Is your AdditionalField3 has some invalid data?|||

yes this field also containing bank name ...we r using this field as common field so here for some cases we storing name and for other date thats why this field is varchar type ..for this purpose we r using format_id..

like this

for 83 we r storing date ..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime) and format_id=83

|||

use the following query..

Code Snippet

--If you use dd/mm/yy

SET DATEFORMAT dmy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 103) Else NULL END = Cast('2006-12-5' as datetime)

--if you use mm/dd/yy

SET DATEFORMAT mdy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 101) Else NULL END = Cast('2006-12-5' as datetime)

|||

getting error

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Then'.

|||Ooops Fixed , try now|||

again error

Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.

|||thanx a lot....gotted now..

Date format

Hi,
i am storing dates as datetime, so let us say the date is 2004-07-15 11:42:43.780. Now i want to retrieve it exactly like this. Unfortunately it doesnot come out like this.
It is coming out like this Jul 15 2004 11:44AM and i'm losing the seconds
and milliseconds.
Could anyone please advise me how to get 2004-07-15 11:42:43.780.
Thanks in advance.
-ssSelect Convert (varchar, Getdate(), 121)|||Thanks you very much|||Hi experts

Can any one help me !!!

I had also having the same type or problem !

select msgdate from emc_messageinfo

Sql server 2000 returns the following
2004-07-01 15:04:10.000

and i need only the current date means "01-07-2004" ,but not in varchar format i need in date format only so that i can use it further

Thanks and regards

Manish Kaushik|||MS-SQL doesn't distinguish between date and time, there is a single datatype that is DATETIME (and a sub-set of it that is SMALLDATETIME). You can truncate the value to either a date or a time to make computation easier, but you can't truly isolate the date from the time. Try using:SELECT Convert(CHAR(10), Getdate(), 121)-PatP

date field not standardize

my problem is about how to standardize format date while storing data

- > my pc set regional setting as mm/dd/yyyy
-> ms sql server just followed this format (i guess)
->date field using data type datetime


i have web page that required user insert date with format dd/mm/yyyy

after that i'll convert that value to ->mm/dd/yyyy

it seem to be okay at first . but when i try to insert this date value it will start to be suck all my day.

date value -> 05/11/2003 (that mean 05 November 2003)
suppose that data store to table -> 11/05/2003

but this is what happen to my table ->05/11/2003 . so it take 05 as month and 11 as day. but when i try insert more then 10 day it's okay 15/12/2003

sorry if my word not very clear. but i really need help on thisIf you are transferring the date as a string then use yyyymmdd - it is unambiguous (yyyy-mm-dd is not).

Date and Time Best Practice - storage in SQL Server database

With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?

For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.

My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?

Thanks in advance for your help!

PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.

Hey, this is an old song.

My point is that you have to store date in a universal format - UTC, this will prevent any troubles while moving database on another zone.

Next, your users have to setup theirs profile and specify in what timezone they are in. According to this you're making shifts.

|||

E.g. look at this site, each user has Site Option section where he/she can setup time zone.

|||

I guess it is that simple.

Do you know of any webservice or other source that has time zone data?

Thanks.

|||

Here is a good article on the subject:

http://aspnet.4guysfromrolla.com/articles/081507-1.aspx