| Author |
Topic |
|
densma
Starting Member
21 Posts |
 Posted - 2004-04-27 : 12:42:21
|
| i am creating a DTS package that will query a table and move data to another table daily on same DB/SERVER. i want to be able to timestamp previous date into date column in destination table. There's no date on source but i need timestamp on each import and date for previous day b/c importing data are for previous day. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-27 : 12:49:33
|
| GETDATE() - 1?Tara |
 |
|
|
densma
Starting Member
21 Posts |
Posted - 2004-04-27 : 13:56:50
|
| thksi have use that under date column formula and it shows yesterday date ok.. i want timestamp when package is running only..wht the best way to use this function to get what im looking for? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-27 : 14:00:20
|
| What do you mean when package is running?Tara |
|
|
|
densma
Starting Member
21 Posts |
Posted - 2004-04-27 : 14:06:42
|
| dts package.. is schedule to run daily using the query to import data into another table daily. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-27 : 14:12:08
|
| When the package is running means you can use GETDATE. GETDATE shows the current system time of the database server.Tara |
|
|
|
densma
Starting Member
21 Posts |
Posted - 2004-04-27 : 14:27:15
|
| thanks anywayi got it..i use under my select query(getdate() - 1) as stampdateit gives me virtual column with date timestamp and them i match it to my date column in the destination table |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-28 : 13:58:50
|
Tara,I don't know why you just don't get the right answer Maybe if you gave it a little more thought....Brett8-) |
|
|
|
densma
Starting Member
21 Posts |
Posted - 2004-04-30 : 13:06:29
|
| Im having problem searching that date field in the table..is it b/c i use date for column name?i tried [date] = '04/12/2004'and[date] like '04/12/2004%'still getting blank output from query Analyzer.data looks like 4/28/2004 12:28:00 PMi changed from datetime to smalldate..still same problem |
|
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-04-30 : 14:03:22
|
| Try select convert(char(10),[date],101) = '04/12/2004' |
|
|
|
densma
Starting Member
21 Posts |
Posted - 2004-04-30 : 15:54:05
|
| so should i just change from smalldate to char in table design view? |
|
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-04-30 : 16:51:14
|
| No. Just use the convert statement in your select:Select * from tablewhere convert(char(10),[date],101) = '04/12/2004'This allows you to use the mm/dd/yyyy format in your select without changing the column type. |
|
|
|
jharwood
Starting Member
41 Posts |
Posted - 2004-04-30 : 16:58:18
|
| you can look in BOL under Cast and Convert for other date formats if you don't like the mm/dd/yyyy format. |
|
|
|
densma
Starting Member
21 Posts |
Posted - 2004-05-03 : 09:50:38
|
| my query(getdate() - 1) as stampdatemakes it return date and time.. how can i make sure that i have date only (mm/dd/yyyy) instead of date/time timestamp in the date column (2002-02-28 10:00:00.000) |
|
|
|
densma
Starting Member
21 Posts |
Posted - 2004-05-03 : 11:11:08
|
| got iti change my query toCONVERT(datetime, CONVERT(CHAR(10), GETDATE()-1, 101)) as stampdateand it returns 05/02/2004 00:00:00:00now i can query[date] = '05/02/2004' and get resultsthanks all for input |
|
|
|
|
date timestamp